I setup a new postgres database (v12) by export/importing from an old one (v9.5).
When I query the new one, it's drastically slower despite being on a more powerful machine. Shared buffers are set to 90 gb, hard drive is 500k iops m.2, 128 threads, etc.
If I run COUNT(*) on both tables, version 12 is over twice as fast. 100 million rows took 3 minutes vs 7.5 minutes.
Nothing jumps out to me from the explain query. What could be the issue?
EXPLAIN ANALYZE from Original Postgres 9.5
"Limit (cost=1422.06..228425.22 rows=1 width=49) (actual time=0.334..2.244 rows=2097 loops=1)"
" -> Bitmap Heap Scan on current_nodes (cost=1422.06..228425.22 rows=1 width=49) (actual time=0.333..2.059 rows=2097 loops=1)"
" Recheck Cond: (((tile >= 1334380527) AND (tile <= 1334380543)) OR ((tile >= 1334381903) AND (tile <= 1334381919)) OR ((tile >= 1334381935) AND (tile <= 1334381951)) OR ((tile >= 1334382032) AND (tile <= 1334382033)) OR ((tile >= 1334382036) AND (tile <= 1334382037)) OR ((tile >= 1334383264) AND (tile <= 1334383295)) OR ((tile >= 1334383328) AND (tile <= 1334383359)) OR ((tile >= 1334383520) AND (tile <= 1334383535)) OR ((tile >= 1334384640) AND (tile <= 1334384769)) OR ((tile >= 1334384772) AND (tile <= 1334384773)) OR ((tile >= 1334384784) AND (tile <= 1334384785)) OR ((tile >= 1334384788) AND (tile <= 1334384789)) OR ((tile >= 1334384832) AND (tile <= 1334384833)) OR ((tile >= 1334384836) AND (tile <= 1334384837)) OR ((tile >= 1334384848) AND (tile <= 1334384849)) OR ((tile >= 1334384852) AND (tile <= 1334384853)) OR ((tile >= 1334384896) AND (tile <= 1334384911)) OR ((tile >= 1334384928) AND (tile <= 1334384943)) OR ((tile >= 1334385024) AND (tile <= 1334385025)) OR ((tile >= 1334385028) AND (tile <= 1334385029)) OR (tile = ANY ('{1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021}'::bigint[])))"
" Filter: (((latitude)::numeric >= 346183300.0) AND ((latitude)::numeric <= 346816700.00000006) AND ((longitude)::numeric >= '-982816700.0'::numeric) AND ((longitude)::numeric <= '-982183300.0'::numeric))"
" Rows Removed by Filter: 286"
" Heap Blocks: exact=204"
" -> BitmapOr (cost=1422.06..1422.06 rows=57888 width=0) (actual time=0.299..0.299 rows=0 loops=1)"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..11.74 rows=317 width=0) (actual time=0.034..0.034 rows=197 loops=1)"
" Index Cond: ((tile >= 1334380527) AND (tile <= 1334380543))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..11.74 rows=317 width=0) (actual time=0.016..0.016 rows=183 loops=1)"
" Index Cond: ((tile >= 1334381903) AND (tile <= 1334381919))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..11.74 rows=317 width=0) (actual time=0.023..0.023 rows=226 loops=1)"
" Index Cond: ((tile >= 1334381935) AND (tile <= 1334381951))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.004..0.004 rows=11 loops=1)"
" Index Cond: ((tile >= 1334382032) AND (tile <= 1334382033))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.002..0.002 rows=11 loops=1)"
" Index Cond: ((tile >= 1334382036) AND (tile <= 1334382037))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..18.71 rows=614 width=0) (actual time=0.025..0.025 rows=307 loops=1)"
" Index Cond: ((tile >= 1334383264) AND (tile <= 1334383295))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..18.71 rows=614 width=0) (actual time=0.017..0.017 rows=232 loops=1)"
" Index Cond: ((tile >= 1334383328) AND (tile <= 1334383359))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..11.54 rows=297 width=0) (actual time=0.016..0.016 rows=86 loops=1)"
" Index Cond: ((tile >= 1334383520) AND (tile <= 1334383535))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..66.11 rows=2553 width=0) (actual time=0.102..0.102 rows=1411 loops=1)"
" Index Cond: ((tile >= 1334384640) AND (tile <= 1334384769))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.002..0.002 rows=0 loops=1)"
" Index Cond: ((tile >= 1334384772) AND (tile <= 1334384773))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.003..0.003 rows=11 loops=1)"
" Index Cond: ((tile >= 1334384784) AND (tile <= 1334384785))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.004..0.004 rows=18 loops=1)"
" Index Cond: ((tile >= 1334384788) AND (tile <= 1334384789))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.004..0.004 rows=14 loops=1)"
" Index Cond: ((tile >= 1334384832) AND (tile <= 1334384833))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.002..0.002 rows=0 loops=1)"
" Index Cond: ((tile >= 1334384836) AND (tile <= 1334384837))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.002..0.002 rows=7 loops=1)"
" Index Cond: ((tile >= 1334384848) AND (tile <= 1334384849))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.003..0.003 rows=0 loops=1)"
" Index Cond: ((tile >= 1334384852) AND (tile <= 1334384853))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..11.54 rows=297 width=0) (actual time=0.004..0.004 rows=34 loops=1)"
" Index Cond: ((tile >= 1334384896) AND (tile <= 1334384911))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..11.54 rows=297 width=0) (actual time=0.010..0.010 rows=117 loops=1)"
" Index Cond: ((tile >= 1334384928) AND (tile <= 1334384943))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.003..0.003 rows=0 loops=1)"
" Index Cond: ((tile >= 1334385024) AND (tile <= 1334385025))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..4.78 rows=20 width=0) (actual time=0.003..0.003 rows=11 loops=1)"
" Index Cond: ((tile >= 1334385028) AND (tile <= 1334385029))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..1196.12 rows=52049 width=0) (actual time=0.020..0.020 rows=74 loops=1)"
" Index Cond: (tile = ANY ('{1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021}'::bigint[]))"
"Planning time: 0.417 ms"
"Execution time: 2.489 ms"
Postgres 12 EXPLAIN ANALYZE
Limit (cost=4140.05..565609.02 rows=4 width=49) (actual time=76.641..114.425 rows=2097 loops=1)
-> Gather (cost=4140.05..565609.02 rows=4 width=49) (actual time=0.713..38.404 rows=2097 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on current_nodes (cost=3140.05..564608.62 rows=2 width=49) (actual time=0.127..0.389 rows=699 loops=3)
Recheck Cond: (((tile >= 1334380527) AND (tile <= 1334380543)) OR ((tile >= 1334381903) AND (tile <= 1334381919)) OR ((tile >= 1334381935) AND (tile <= 1334381951)) OR ((tile >= 1334382032) AND (tile <= 1334382033)) OR ((tile >= 1334382036) AND (tile <= 1334382037)) OR ((tile >= 1334383264) AND (tile <= 1334383295)) OR ((tile >= 1334383328) AND (tile <= 1334383359)) OR ((tile >= 1334383520) AND (tile <= 1334383535)) OR ((tile >= 1334384640) AND (tile <= 1334384769)) OR ((tile >= 1334384772) AND (tile <= 1334384773)) OR ((tile >= 1334384784) AND (tile <= 1334384785)) OR ((tile >= 1334384788) AND (tile <= 1334384789)) OR ((tile >= 1334384832) AND (tile <= 1334384833)) OR ((tile >= 1334384836) AND (tile <= 1334384837)) OR ((tile >= 1334384848) AND (tile <= 1334384849)) OR ((tile >= 1334384852) AND (tile <= 1334384853)) OR ((tile >= 1334384896) AND (tile <= 1334384911)) OR ((tile >= 1334384928) AND (tile <= 1334384943)) OR ((tile >= 1334385024) AND (tile <= 1334385025)) OR ((tile >= 1334385028) AND (tile <= 1334385029)) OR (tile = ANY ('{1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021}'::bigint[])))
Filter: (((latitude)::numeric >= 346183300.0) AND ((latitude)::numeric <= 346816700.00000006) AND ((longitude)::numeric >= '-982816700.0'::numeric) AND ((longitude)::numeric <= '-982183300.0'::numeric))
Rows Removed by Filter: 95
Heap Blocks: exact=189
-> BitmapOr (cost=3140.05..3140.05 rows=152916 width=0) (actual time=0.332..0.335 rows=0 loops=1)
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.80 rows=5122 width=0) (actual time=0.216..0.216 rows=191 loops=1)
Index Cond: ((tile >= 1334380527) AND (tile <= 1334380543))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.80 rows=5122 width=0) (actual time=0.009..0.009 rows=180 loops=1)
Index Cond: ((tile >= 1334381903) AND (tile <= 1334381919))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.80 rows=5122 width=0) (actual time=0.006..0.006 rows=154 loops=1)
Index Cond: ((tile >= 1334381935) AND (tile <= 1334381951))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.003 rows=11 loops=1)
Index Cond: ((tile >= 1334382032) AND (tile <= 1334382033))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.002..0.002 rows=11 loops=1)
Index Cond: ((tile >= 1334382036) AND (tile <= 1334382037))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..112.39 rows=5182 width=0) (actual time=0.011..0.011 rows=286 loops=1)
Index Cond: ((tile >= 1334383264) AND (tile <= 1334383295))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..112.39 rows=5182 width=0) (actual time=0.008..0.008 rows=219 loops=1)
Index Cond: ((tile >= 1334383328) AND (tile <= 1334383359))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.76 rows=5118 width=0) (actual time=0.007..0.007 rows=81 loops=1)
Index Cond: ((tile >= 1334383520) AND (tile <= 1334383535))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..120.35 rows=5577 width=0) (actual time=0.035..0.035 rows=1042 loops=1)
Index Cond: ((tile >= 1334384640) AND (tile <= 1334384769))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((tile >= 1334384772) AND (tile <= 1334384773))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.001..0.001 rows=11 loops=1)
Index Cond: ((tile >= 1334384784) AND (tile <= 1334384785))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.002..0.002 rows=18 loops=1)
Index Cond: ((tile >= 1334384788) AND (tile <= 1334384789))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.003 rows=7 loops=1)
Index Cond: ((tile >= 1334384832) AND (tile <= 1334384833))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((tile >= 1334384836) AND (tile <= 1334384837))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.001..0.002 rows=4 loops=1)
Index Cond: ((tile >= 1334384848) AND (tile <= 1334384849))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((tile >= 1334384852) AND (tile <= 1334384853))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.76 rows=5118 width=0) (actual time=0.003..0.003 rows=28 loops=1)
Index Cond: ((tile >= 1334384896) AND (tile <= 1334384911))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.76 rows=5118 width=0) (actual time=0.004..0.004 rows=68 loops=1)
Index Cond: ((tile >= 1334384928) AND (tile <= 1334384943))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((tile >= 1334385024) AND (tile <= 1334385025))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.002..0.002 rows=11 loops=1)
Index Cond: ((tile >= 1334385028) AND (tile <= 1334385029))
-> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..945.10 rows=50576 width=0) (actual time=0.013..0.013 rows=61 loops=1)
Index Cond: (tile = ANY ('{1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021}'::bigint[]))
Planning Time: 0.338 ms
JIT:
Functions: 13
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 4.516 ms, Inlining 4.039 ms, Optimization 43.691 ms, Emission 28.069 ms, Total 80.315 ms
Execution Time: 116.372 ms
Edit: I disabled JIT, which improved it, but it's still significantly slower.
"Limit (cost=4140.05..565609.02 rows=4 width=49) (actual time=1.434..36.111 rows=2097 loops=1)"
" -> Gather (cost=4140.05..565609.02 rows=4 width=49) (actual time=1.433..35.884 rows=2097 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Bitmap Heap Scan on current_nodes (cost=3140.05..564608.62 rows=2 width=49) (actual time=0.219..0.905 rows=699 loops=3)"
" Recheck Cond: (((tile >= 1334380527) AND (tile <= 1334380543)) OR ((tile >= 1334381903) AND (tile <= 1334381919)) OR ((tile >= 1334381935) AND (tile <= 1334381951)) OR ((tile >= 1334382032) AND (tile <= 1334382033)) OR ((tile >= 1334382036) AND (tile <= 1334382037)) OR ((tile >= 1334383264) AND (tile <= 1334383295)) OR ((tile >= 1334383328) AND (tile <= 1334383359)) OR ((tile >= 1334383520) AND (tile <= 1334383535)) OR ((tile >= 1334384640) AND (tile <= 1334384769)) OR ((tile >= 1334384772) AND (tile <= 1334384773)) OR ((tile >= 1334384784) AND (tile <= 1334384785)) OR ((tile >= 1334384788) AND (tile <= 1334384789)) OR ((tile >= 1334384832) AND (tile <= 1334384833)) OR ((tile >= 1334384836) AND (tile <= 1334384837)) OR ((tile >= 1334384848) AND (tile <= 1334384849)) OR ((tile >= 1334384852) AND (tile <= 1334384853)) OR ((tile >= 1334384896) AND (tile <= 1334384911)) OR ((tile >= 1334384928) AND (tile <= 1334384943)) OR ((tile >= 1334385024) AND (tile <= 1334385025)) OR ((tile >= 1334385028) AND (tile <= 1334385029)) OR (tile = ANY ('{1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021}'::bigint[])))"
" Filter: (((latitude)::numeric >= 346183300.0) AND ((latitude)::numeric <= 346816700.00000006) AND ((longitude)::numeric >= '-982816700.0'::numeric) AND ((longitude)::numeric <= '-982183300.0'::numeric))"
" Rows Removed by Filter: 95"
" Heap Blocks: exact=189"
" -> BitmapOr (cost=3140.05..3140.05 rows=152916 width=0) (actual time=0.577..0.580 rows=0 loops=1)"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.80 rows=5122 width=0) (actual time=0.323..0.323 rows=191 loops=1)"
" Index Cond: ((tile >= 1334380527) AND (tile <= 1334380543))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.80 rows=5122 width=0) (actual time=0.018..0.018 rows=180 loops=1)"
" Index Cond: ((tile >= 1334381903) AND (tile <= 1334381919))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.80 rows=5122 width=0) (actual time=0.013..0.013 rows=154 loops=1)"
" Index Cond: ((tile >= 1334381935) AND (tile <= 1334381951))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.005..0.005 rows=11 loops=1)"
" Index Cond: ((tile >= 1334382032) AND (tile <= 1334382033))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.004 rows=11 loops=1)"
" Index Cond: ((tile >= 1334382036) AND (tile <= 1334382037))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..112.39 rows=5182 width=0) (actual time=0.023..0.023 rows=286 loops=1)"
" Index Cond: ((tile >= 1334383264) AND (tile <= 1334383295))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..112.39 rows=5182 width=0) (actual time=0.017..0.017 rows=219 loops=1)"
" Index Cond: ((tile >= 1334383328) AND (tile <= 1334383359))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.76 rows=5118 width=0) (actual time=0.011..0.011 rows=81 loops=1)"
" Index Cond: ((tile >= 1334383520) AND (tile <= 1334383535))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..120.35 rows=5577 width=0) (actual time=0.076..0.076 rows=1042 loops=1)"
" Index Cond: ((tile >= 1334384640) AND (tile <= 1334384769))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.003 rows=0 loops=1)"
" Index Cond: ((tile >= 1334384772) AND (tile <= 1334384773))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.004..0.004 rows=11 loops=1)"
" Index Cond: ((tile >= 1334384784) AND (tile <= 1334384785))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.004..0.004 rows=18 loops=1)"
" Index Cond: ((tile >= 1334384788) AND (tile <= 1334384789))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.004..0.004 rows=7 loops=1)"
" Index Cond: ((tile >= 1334384832) AND (tile <= 1334384833))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.003 rows=0 loops=1)"
" Index Cond: ((tile >= 1334384836) AND (tile <= 1334384837))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.003 rows=4 loops=1)"
" Index Cond: ((tile >= 1334384848) AND (tile <= 1334384849))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.002..0.002 rows=0 loops=1)"
" Index Cond: ((tile >= 1334384852) AND (tile <= 1334384853))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.76 rows=5118 width=0) (actual time=0.007..0.007 rows=28 loops=1)"
" Index Cond: ((tile >= 1334384896) AND (tile <= 1334384911))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..111.76 rows=5118 width=0) (actual time=0.008..0.008 rows=68 loops=1)"
" Index Cond: ((tile >= 1334384928) AND (tile <= 1334384943))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.004..0.004 rows=0 loops=1)"
" Index Cond: ((tile >= 1334385024) AND (tile <= 1334385025))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..107.20 rows=5062 width=0) (actual time=0.003..0.003 rows=11 loops=1)"
" Index Cond: ((tile >= 1334385028) AND (tile <= 1334385029))"
" -> Bitmap Index Scan on current_nodes_tile_idx (cost=0.00..945.10 rows=50576 width=0) (actual time=0.040..0.040 rows=61 loops=1)"
" Index Cond: (tile = ANY ('{1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021}'::bigint[]))"
"Planning Time: 0.376 ms"
"Execution Time: 36.307 ms"
analyze
all tables after the import?analyze
and make sure indexes are not corrupted , maybe do a reindex