I recently came across this neat trick for speeding up counts when I don't need an exact result. The basic idea is that you simply prepend your query (sans count) with EXPLAIN to trick the query planner into telling you what it thinks the row count would be. So I started using it in production, mostly on a single large table.
A week later, I ran into a problem with the query planner in production (but not locally with the same data) deciding to do a bunch of nested loop joins when hash joins are perfectly serviceable. In the short term, I'm setting enable_nestloop to false for that query, but could it be that my overuse of EXPLAIN is confusing the planner?
I recently upgraded from version 11 to 14 as well.
Edit: here are the query plans between local (or enable_nestloop = false) and production:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4420.71..4420.77 rows=25 width=289)
-> Sort (cost=4420.71..4440.50 rows=7917 width=289)
Sort Key: line_item_t.created DESC
-> Hash Left Join (cost=3372.35..4197.30 rows=7917 width=289)
Hash Cond: (till_report_t.till = till_t.id)
-> Hash Left Join (cost=3347.72..3835.07 rows=7917 width=584)
Hash Cond: (transaction_t.till_report = till_report_t.id)
-> Hash Left Join (cost=3345.58..3810.42 rows=7917 width=568)
Hash Cond: (transaction_t.customer = customer_t.id)
-> Hash Left Join (cost=3308.40..3752.44 rows=7917 width=533)
Hash Cond: (item_t.supplier = supplier_t.id)
-> Hash Left Join (cost=3271.23..3694.32 rows=7917 width=498)
Hash Cond: (item_t.shelf = shelf_t.id)
-> Hash Left Join (cost=3249.53..3651.80 rows=7917 width=466)
Hash Cond: (item_t.category = category_t.id)
-> Hash Left Join (cost=3242.39..3623.40 rows=7917 width=450)
Hash Cond: (line_item_t.id = taxes_t.line_item)
-> Hash Left Join (cost=1902.78..2263.00 rows=7917 width=418)
Hash Cond: (line_item_t.id = discounts_t.line_item)
-> Hash Left Join (cost=1423.73..1763.15 rows=7917 width=386)
Hash Cond: (line_item_t.id = surcharges_t.line_item)
-> Hash Left Join (cost=866.36..1184.99 rows=7917 width=354)
Hash Cond: (line_item_t.item = item_t.id)
-> Hash Left Join (cost=271.24..569.07 rows=7917 width=192)
Hash Cond: (line_item_t.transaction = transaction_t.id)
Filter: (transaction_t.status IS DISTINCT FROM 'voided'::text)
-> Seq Scan on line_item line_item_t (cost=0.00..277.01 rows=7921 width=100)
Filter: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=219.44..219.44 rows=4144 width=92)
-> Seq Scan on transaction transaction_t (cost=0.00..219.44 rows=4144 width=92)
-> Hash (cost=521.72..521.72 rows=5872 width=162)
-> Seq Scan on item item_t (cost=0.00..521.72 rows=5872 width=162)
-> Hash (cost=513.99..513.99 rows=3471 width=48)
-> Subquery Scan on surcharges_t (cost=392.50..513.99 rows=3471 width=48)
-> GroupAggregate (cost=392.50..479.28 rows=3471 width=48)
Group Key: applied_surcharge_t.line_item
-> Sort (cost=392.50..401.18 rows=3471 width=52)
Sort Key: applied_surcharge_t.line_item
-> Seq Scan on applied_surcharge applied_surcharge_t (cost=0.00..188.39 rows=3471 width=52)
Filter: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=447.41..447.41 rows=2531 width=48)
-> Subquery Scan on discounts_t (cost=357.89..447.41 rows=2531 width=48)
-> GroupAggregate (cost=357.89..422.10 rows=2531 width=48)
Group Key: applied_discount_t.line_item
-> Sort (cost=357.89..364.45 rows=2625 width=52)
Sort Key: applied_discount_t.line_item
-> Seq Scan on applied_discount applied_discount_t (cost=0.00..208.81 rows=2625 width=52)
Filter: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=1241.22..1241.22 rows=7871 width=48)
-> Subquery Scan on taxes_t (cost=965.73..1241.22 rows=7871 width=48)
-> GroupAggregate (cost=965.73..1162.51 rows=7871 width=48)
Group Key: applied_tax_t.line_item
-> Sort (cost=965.73..985.41 rows=7871 width=52)
Sort Key: applied_tax_t.line_item
-> Seq Scan on applied_tax applied_tax_t (cost=0.00..456.39 rows=7871 width=52)
Filter: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=4.84..4.84 rows=184 width=32)
-> Seq Scan on category category_t (cost=0.00..4.84 rows=184 width=32)
-> Hash (cost=15.20..15.20 rows=520 width=48)
-> Seq Scan on shelf shelf_t (cost=0.00..15.20 rows=520 width=48)
-> Hash (cost=30.41..30.41 rows=541 width=51)
-> Seq Scan on contact supplier_t (cost=0.00..30.41 rows=541 width=51)
-> Hash (cost=30.41..30.41 rows=541 width=51)
-> Seq Scan on contact customer_t (cost=0.00..30.41 rows=541 width=51)
-> Hash (cost=1.51..1.51 rows=51 width=32)
-> Seq Scan on till_report till_report_t (cost=0.00..1.51 rows=51 width=32)
-> Hash (cost=16.50..16.50 rows=650 width=48)
-> Seq Scan on till till_t (cost=0.00..16.50 rows=650 width=48)
(68 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2359.85..2359.87 rows=1 width=289)
-> Result (cost=2359.85..2359.87 rows=1 width=289)
-> Sort (cost=2359.85..2359.85 rows=1 width=409)
Sort Key: line_item_t.created DESC
-> Nested Loop Left Join (cost=2271.47..2359.85 rows=1 width=409)
Join Filter: (till_report_t.till = till_t.id)
-> Nested Loop Left Join (cost=2271.47..2358.43 rows=1 width=480)
Join Filter: (transaction_t.till_report = till_report_t.id)
-> Nested Loop Left Join (cost=2271.47..2357.09 rows=1 width=464)
-> Nested Loop Left Join (cost=2271.41..2357.02 rows=1 width=440)
-> Nested Loop Left Join (cost=2271.35..2356.95 rows=1 width=416)
Join Filter: (item_t.shelf = shelf_t.id)
-> Nested Loop Left Join (cost=2271.35..2355.94 rows=1 width=413)
Join Filter: (item_t.category = category_t.id)
-> Nested Loop Left Join (cost=2271.35..2347.32 rows=1 width=401)
Join Filter: (applied_tax_t.line_item = line_item_t.id)
-> Nested Loop Left Join (cost=2267.26..2343.21 rows=1 width=369)
Join Filter: (applied_discount_t.line_item = line_item_t.id)
-> Nested Loop Left Join (cost=1378.01..1426.46 rows=1 width=337)
Join Filter: (applied_surcharge_t.line_item = line_item_t.id)
-> Nested Loop Left Join (cost=0.23..12.24 rows=1 width=305)
Filter: (transaction_t.status IS DISTINCT FROM 'voided'::text)
-> Nested Loop Left Join (cost=0.17..8.18 rows=1 width=213)
-> Index Scan using line_item_store_index on line_item line_item_t (cost=0.08..4.09 rows=1 width=100)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Index Scan using item_pk on item item_t (cost=0.08..4.09 rows=1 width=113)
Index Cond: (id = line_item_t.item)
-> Index Scan using transaction_pk on transaction transaction_t (cost=0.06..4.06 rows=1 width=92)
Index Cond: (id = line_item_t.transaction)
-> HashAggregate (cost=1377.79..1391.67 rows=3470 width=48)
Group Key: applied_surcharge_t.line_item
-> Bitmap Heap Scan on applied_surcharge applied_surcharge_t (cost=39.44..1372.58 rows=3470 width=52)
Recheck Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Bitmap Index Scan on applied_surcharge_store_index (cost=0.00..39.26 rows=3470 width=0)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> HashAggregate (cost=889.25..899.73 rows=2619 width=48)
Group Key: applied_discount_t.line_item
-> Bitmap Heap Scan on applied_discount applied_discount_t (cost=30.13..885.31 rows=2625 width=52)
Recheck Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Bitmap Index Scan on applied_discount_store_index (cost=0.00..30.00 rows=2625 width=0)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> GroupAggregate (cost=4.09..4.10 rows=1 width=48)
Group Key: applied_tax_t.line_item
-> Sort (cost=4.09..4.09 rows=1 width=52)
Sort Key: applied_tax_t.line_item
-> Index Scan using applied_tax_store_index on applied_tax applied_tax_t (cost=0.08..4.09 rows=1 width=52)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Seq Scan on category category_t (cost=0.00..7.21 rows=404 width=28)
-> Seq Scan on shelf shelf_t (cost=0.00..1.00 rows=1 width=19)
-> Index Scan using contact_pk on contact supplier_t (cost=0.06..0.07 rows=1 width=40)
Index Cond: (id = item_t.supplier)
-> Index Scan using contact_pk on contact customer_t (cost=0.06..0.08 rows=1 width=40)
Index Cond: (id = transaction_t.customer)
-> Seq Scan on till_report till_report_t (cost=0.00..1.15 rows=51 width=32)
-> Seq Scan on till till_t (cost=0.00..1.20 rows=65 width=48)
(55 rows)
Edit 2: I ran ANALYZE on all the affected tables and my query is running dramatically faster. The query plan still isn't using hash left joins, but it is using indexes/memoize rather than seq scans:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=11884.38..11950.45 rows=25 width=289) (actual time=103.418..109.963 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.38..33470.79 rows=8169 width=289) (actual time=103.417..109.958 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.35..33352.07 rows=8169 width=487) (actual time=103.274..109.286 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.33..33085.69 rows=8169 width=471) (actual time=103.261..109.239 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.27..32921.60 rows=8169 width=447) (actual time=103.250..109.190 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.21..32384.36 rows=8169 width=423) (actual time=103.216..108.979 rows=25 loops=1)
Join Filter: (item_t.shelf = shelf_t.id)
Rows Removed by Join Filter: 89
-> Nested Loop Left Join (cost=11884.21..32221.21 rows=8169 width=415) (actual time=103.201..108.940 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.15..32147.78 rows=8169 width=403) (actual time=103.180..108.856 rows=25 loops=1)
-> Nested Loop Left Join (cost=11884.07..17137.79 rows=8169 width=288) (actual time=103.146..108.516 rows=25 loops=1)
Filter: (transaction_t.status IS DISTINCT FROM 'voided'::text)
-> Gather Merge (cost=11884.01..12760.26 rows=8205 width=196) (actual time=103.106..108.353 rows=25 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=10884.01..10886.42 rows=4826 width=196) (actual time=98.270..98.286 rows=84 loops=2)
Sort Key: line_item_t.created DESC
Sort Method: quicksort Memory: 4244kB
Worker 0: Sort Method: quicksort Memory: 1037kB
-> Hash Left Join (cost=7165.46..10824.95 rows=4826 width=196) (actual time=85.724..93.546 rows=3962 loops=2)
Hash Cond: (line_item_t.id = taxes_t.line_item)
-> Hash Left Join (cost=2423.36..6080.32 rows=4826 width=164) (actual time=36.150..42.054 rows=3962 loops=2)
Hash Cond: (line_item_t.id = discounts_t.line_item)
-> Hash Left Join (cost=1506.60..5161.02 rows=4826 width=132) (actual time=20.669..24.920 rows=3962 loops=2)
Hash Cond: (line_item_t.id = surcharges_t.line_item)
-> Parallel Bitmap Heap Scan on line_item line_item_t (cost=92.80..3744.69 rows=4826 width=100) (actual time=0.465..2.835 rows=3962 loops=2)
Recheck Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
Heap Blocks: exact=154
-> Bitmap Index Scan on line_item_store_index (cost=0.00..92.39 rows=8205 width=0) (actual time=0.833..0.833 rows=7923 loops=1)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=1401.74..1401.74 rows=3445 width=48) (actual time=20.168..20.170 rows=3472 loops=2)
Buckets: 4096 Batches: 1 Memory Usage: 756kB
-> Subquery Scan on surcharges_t (cost=1377.62..1401.74 rows=3445 width=48) (actual time=15.096..18.776 rows=3472 loops=2)
-> HashAggregate (cost=1377.62..1391.40 rows=3445 width=48) (actual time=15.095..18.299 rows=3472 loops=2)
Group Key: applied_surcharge_t.line_item
Batches: 1 Memory Usage: 2257kB
Worker 0: Batches: 1 Memory Usage: 2257kB
-> Bitmap Heap Scan on applied_surcharge applied_surcharge_t (cost=39.40..1372.45 rows=3445 width=52) (actual time=0.355..2.048 rows=3472 loops=2)
Recheck Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
Heap Blocks: exact=175
-> Bitmap Index Scan on applied_surcharge_store_index (cost=0.00..39.23 rows=3445 width=0) (actual time=0.314..0.314 rows=3473 loops=2)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=907.60..907.60 rows=2620 width=48) (actual time=15.453..15.455 rows=2532 loops=2)
Buckets: 4096 Batches: 1 Memory Usage: 575kB
-> Subquery Scan on discounts_t (cost=889.26..907.60 rows=2620 width=48) (actual time=11.729..14.389 rows=2532 loops=2)
-> HashAggregate (cost=889.26..899.74 rows=2620 width=48) (actual time=11.728..14.067 rows=2532 loops=2)
Group Key: applied_discount_t.line_item
Batches: 1 Memory Usage: 2161kB
Worker 0: Batches: 1 Memory Usage: 2161kB
-> Bitmap Heap Scan on applied_discount applied_discount_t (cost=30.13..885.32 rows=2626 width=52) (actual time=0.262..1.828 rows=2626 loops=2)
Recheck Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
Heap Blocks: exact=176
-> Bitmap Index Scan on applied_discount_store_index (cost=0.00..30.00 rows=2626 width=0) (actual time=0.226..0.227 rows=2627 loops=2)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Hash (cost=4714.02..4714.02 rows=8023 width=48) (actual time=49.510..49.512 rows=7873 loops=2)
Buckets: 8192 Batches: 1 Memory Usage: 1660kB
-> Subquery Scan on taxes_t (cost=4657.85..4714.02 rows=8023 width=48) (actual time=37.618..46.171 rows=7873 loops=2)
-> HashAggregate (cost=4657.85..4689.95 rows=8023 width=48) (actual time=37.617..45.174 rows=7873 loops=2)
Group Key: applied_tax_t.line_item
Batches: 1 Memory Usage: 8593kB
Worker 0: Batches: 1 Memory Usage: 8593kB
-> Bitmap Heap Scan on applied_tax applied_tax_t (cost=90.52..4645.82 rows=8023 width=52) (actual time=0.915..4.077 rows=7873 loops=2)
Recheck Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
Heap Blocks: exact=361
-> Bitmap Index Scan on applied_tax_store_index (cost=0.00..90.12 rows=8023 width=0) (actual time=0.857..0.857 rows=7873 loops=2)
Index Cond: (store = '49b2980b-9e62-4c49-b42b-a862d25852a8'::uuid)
-> Index Scan using transaction_pk on transaction transaction_t (cost=0.06..0.53 rows=1 width=92) (actual time=0.006..0.006 rows=1 loops=25)
Index Cond: (id = line_item_t.transaction)
-> Index Scan using item_pk on item item_t (cost=0.08..1.84 rows=1 width=115) (actual time=0.013..0.013 rows=1 loops=25)
Index Cond: (id = line_item_t.item)
-> Memoize (cost=0.06..0.06 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=25)
Cache Key: item_t.category
Cache Mode: logical
Hits: 9 Misses: 16 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using category_pk on category category_t (cost=0.05..0.06 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=16)
Index Cond: (id = item_t.category)
-> Materialize (cost=0.00..1.02 rows=5 width=24) (actual time=0.000..0.001 rows=4 loops=25)
-> Seq Scan on shelf shelf_t (cost=0.00..1.01 rows=5 width=24) (actual time=0.009..0.011 rows=5 loops=1)
-> Index Scan using contact_pk on contact supplier_t (cost=0.06..0.07 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=25)
Index Cond: (id = item_t.supplier)
-> Memoize (cost=0.06..0.08 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=25)
Cache Key: transaction_t.customer
Cache Mode: logical
Hits: 22 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using contact_pk on contact customer_t (cost=0.06..0.08 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=3)
Index Cond: (id = transaction_t.customer)
-> Index Scan using till_report_pkey on till_report till_report_t (cost=0.03..0.03 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=25)
Index Cond: (id = transaction_t.till_report)
-> Memoize (cost=0.03..0.15 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=25)
Cache Key: till_report_t.till
Cache Mode: logical
Hits: 24 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using till_pk on till till_t (cost=0.03..0.15 rows=1 width=25) (actual time=0.022..0.022 rows=1 loops=1)
Index Cond: (id = till_report_t.till)
Planning Time: 11.515 ms
Execution Time: 111.673 ms
(96 rows)