On 6/4/20 9:43 AM, Tom Lane wrote:
Koen De Groote <kdg.dev@xxxxxxxxx> writes:
I've got a table with about 30 million rows and a particular index that up
until recently was actively being used.
And then it stopped being used and the query that the index was made for,
is now doing sequential scans.
Deleting the index and creating it again, seems to fix the problem. The new
index, which is identical in composition, is being used and the query in
question no longer uses sequential scans.
It's possible that the index had bloated to the point where the planner
thought it was cheaper to use a seqscan. Did you make a note of the
cost estimates for the different plans?
I missed the part where the OP pointed to a SO question. In that
question where links to explain.depesz.com output. So:
With index(https://explain.depesz.com/s/H5X9y):
Limit (cost=5964059.790..5964071.460 rows=100 width=3141) (actual
time=2534.648..2547.352 rows=100 loops=1)
Buffers: shared hit=30 read=27753
-> Gather Merge (cost=5959707.820..6516383.180 rows=4771170
width=3141) (actual time=2193.611..2542.835 rows=37400 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=30 read=27753
-> Sort (cost=5958707.790..5964671.760 rows=2385585
width=3141) (actual time=1833.278..1838.731 rows=12528 loops=3)
Sort Key: mike_romeo, quebec
Sort Method: quicksort Memory: 24449kB
Worker 0: Sort Method: quicksort Memory: 28634kB
Worker 1: Sort Method: quicksort Memory: 18065kB
Buffers: shared hit=30 read=27753
-> Parallel Bitmap Heap Scan on oscar mike_three
(cost=4282.840..1923544.730 rows=2385585 width=3141) (actual
time=150.297..659.047 rows=23586 loops=3)
Recheck Cond: ((zulu <= 'echo'::timestamp without
time zone) AND four AND (NOT bravo))
Heap Blocks: exact=9759
Buffers: shared read=27753
-> Bitmap Index Scan on foxtrot
(cost=0.000..2851.490 rows=5725405 width=0) (actual
time=245.459..245.459 rows=70759 loops=1)
Index Cond: (zulu <= 'echo'::timestamp
without time zone)
Buffers: shared read=2028
Planning time: 437.187 ms
Execution time: 2549.633 ms
W/O index(https://explain.depesz.com/s/n6bP):
Limit (cost=5975083.560..5975095.230 rows=100 width=3141) (actual
time=159708.476..159718.368 rows=100 loops=1)
Buffers: shared hit=1082 read=1798654
-> Gather Merge (cost=5970731.590..6527406.950 rows=4771170
width=3141) (actual time=159309.819..159713.861 rows=37400 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1082 read=1798654
-> Sort (cost=5969731.570..5975695.530 rows=2385585
width=3141) (actual time=159214.623..159219.579 rows=12531 loops=3)
Sort Key: mike_romeo, quebec
Sort Method: quicksort Memory: 24459kB
Worker 0: Sort Method: quicksort Memory: 22650kB
Worker 1: Sort Method: quicksort Memory: 24038kB
Buffers: shared hit=1082 read=1798654
-> Parallel Seq Scan on oscar mike_three
(cost=0.000..1934568.500 rows=2385585 width=3141) (actual
time=159.800..158018.961 rows=23586 loops=3)
Filter: (four AND (NOT bravo) AND (zulu <=
'echo'::timestamp without time zone))
Rows Removed by Filter: 8610174
Buffers: shared hit=1014 read=1798652
Planning time: 0.807 ms
Execution time: 159720.208 ms
regards, tom lane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
