psql: add useful indexes

[jzelinskie]

This adds some missing UNIQUE constraints and indexes for the vulnerability table that should improve query performance.

[Quentin-M]

As discussed offline, this indeed looks good. There are few missing indexes/constraints here and there but more notably, this should notably fix the following issue we've been facing recently:

pgsql: searchVulnerabilityBase+searchVulnerabilityByNamespaceAndName+searchVulnerabilityForUpdate.Scan(): pq: canceling statement due to statement timeout"

EXPLAIN ANALYZE
SELECT v.id, v.name, n.id, n.name, v.description, v.link, v.severity, v.metadata
FROM Vulnerability v JOIN Namespace n ON v.namespace_id = n.id 
WHERE n.name = "debian:8" AND v.name = "CVE-2015-7985" AND v.deleted_at IS NULL
FOR UPDATE OF v

LockRows  (cost=0.00..103172.53 rows=1 width=412) (actual time=132031.826..140400.572 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..103172.52 rows=1 width=412) (actual time=131894.559..140263.301 rows=1 loops=1)
        Join Filter: (v.namespace_id = n.id)
        Rows Removed by Join Filter: 5
        ->  Seq Scan on namespace n  (cost=0.00..1.88 rows=1 width=23) (actual time=0.014..0.025 rows=1 loops=1)
              Filter: ((name)::text = 'debian:8'::text)
              Rows Removed by Filter: 101
        ->  Seq Scan on vulnerability v  (cost=0.00..103170.56 rows=7 width=393) (actual time=42501.178..140263.223 rows=6 loops=1)
              Filter: ((deleted_at IS NULL) AND ((name)::text = 'CVE-2015-7985'::text))
              Rows Removed by Filter: 1393377
Planning time: 4.192 ms
Execution time: 140400.695 ms

I believe that the Vulnerability (namespace_id, name) index was inadvertently removed when I worked on the notification system (deleted_at) and had to remove the unique constraint (which maintained an index) on these two columns.

After reestablishing the index, the query becomes:

LockRows  (cost=5.08..347.85 rows=1 width=412) (actual time=1.124..1.140 rows=1 loops=1)
  ->  Nested Loop  (cost=5.08..347.84 rows=1 width=412) (actual time=1.091..1.105 rows=1 loops=1)
        Join Filter: (v.namespace_id = n.id)
        Rows Removed by Join Filter: 5
        ->  Seq Scan on namespace n  (cost=0.00..1.88 rows=1 width=23) (actual time=0.010..0.020 rows=1 loops=1)
              Filter: ((name)::text = 'debian:8'::text)
              Rows Removed by Filter: 101
        ->  Bitmap Heap Scan on vulnerability v  (cost=5.08..345.88 rows=7 width=393) (actual time=0.426..1.059 rows=6 loops=1)
              Recheck Cond: ((name)::text = 'CVE-2015-7985'::text)
              Filter: (deleted_at IS NULL)
              Rows Removed by Filter: 75
              Heap Blocks: exact=75
              ->  Bitmap Index Scan on vulnerability_name_idx  (cost=0.00..5.08 rows=87 width=0) (actual time=0.114..0.114 rows=81 loops=1)
                    Index Cond: ((name)::text = 'CVE-2015-7985'::text)
Planning time: 0.407 ms
Execution time: 1.199 ms

Bravo!