Much faster DB garbage-collection queries
Much faster DB garbage-collection queries

Previously, our database cleanup queries were requiring 2 sequential scans a
piece, each with no filtering. By rewriting those queries to use sql's EXISTS
operator, we can leverage Postgres' hash-ant-join feature to speed things up
considerably.

On my large-ish database containing ~28k resource parameters, this optimization
cut resource-parameter GC time from around 1000 seconds (!) to around 77
milliseconds.

Here is the query plan for the old parameter GC query:

grayskull=# EXPLAIN SELECT * FROM resource_params WHERE resource NOT IN (SELECT resource FROM catalog_resources);
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Seq Scan on resource_params  (cost=0.00..28099275.21 rows=13794 width=84)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..1971.38 rows=26225 width=41)
           ->  Seq Scan on catalog_resources  (cost=0.00..1609.25 rows=26225 width=41)
(5 rows)

Notice the cost estimate of the outer sequential scan. Here's the plan for the
revised query:

grayskull=# EXPLAIN SELECT * FROM resource_params rp WHERE NOT EXISTS (SELECT * FROM catalog_resources cr WHERE cr.resource=rp.resource);
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Anti Join  (cost=2168.06..4275.27 rows=1 width=84)
   Hash Cond: ((rp.resource)::text = (cr.resource)::text)
   ->  Seq Scan on resource_params rp  (cost=0.00..671.89 rows=27589 width=84)
   ->  Hash  (cost=1609.25..1609.25 rows=26225 width=41)
         ->  Seq Scan on catalog_resources cr  (cost=0.00..1609.25 rows=26225 width=41)
(5 rows)

Much nicer!

Signed-off-by: Deepak Giridharagopal <d11186354d1ef01ca06ae37d7e23e827da13e85f@puppetlabs.com>

melissa/puppetdb,melissa/puppetdb,stahnma/puppetdb,grimradical/puppetdb,rbrw/puppetdb,haus/puppetdb,kbarber/puppetdb,grimradical/puppetdb,jantman/puppetdb,puppetlabs/puppetdb,cprice404/puppetdb,kbrezina/puppetdb,highb/puppetdb,mullr/puppetdb,ajroetker/puppetdb,waynr/puppetdb,wkalt/puppetdb,jantman/puppetdb,highb/puppetdb,johnduarte/puppetdb,wkalt/puppetdb,ajroetker/puppetdb,shrug/puppetdb,cprice404/puppetdb,github/puppetlabs-puppetdb,senior/puppetdb,kbrezina/puppetdb,puppetlabs/puppetdb,kbrezina/puppetdb,wkalt/puppetdb,rbrw/puppetdb,mullr/puppetdb,mullr/puppetdb,ajroetker/puppetdb,waynr/puppetdb,mullr/puppetdb,github/puppetlabs-puppetdb,haus/puppetdb,ajroetker/puppetdb,senior/puppetdb,nfagerlund/puppetdb,puppetlabs/puppetdb,johnduarte/puppetdb,rbrw/puppetdb,shrug/puppetdb,github/puppetlabs-puppetdb,senior/puppetdb,puppetlabs/puppetdb,kbarber/puppetdb,rbrw/puppetdb,mullr/puppetdb,shrug/puppetdb,senior/puppetdb,waynr/puppetdb,kbrezina/puppetdb,shrug/puppetdb,jantman/puppetdb,kbarber/puppetdb,puppetlabs/puppetdb,highb/puppetdb,johnduarte/puppetdb,wkalt/puppetdb,nfagerlund/puppetdb,rbrw/puppetdb,melissa/puppetdb,johnduarte/puppetdb,grimradical/puppetdb,cprice404/puppetdb,grimradical/puppetdb,kbarber/puppetdb,stahnma/puppetdb,highb/puppetdb,haus/puppetdb,nfagerlund/puppetdb,waynr/puppetdb,stahnma/puppetdb