Re: Index no longer being used, destroying and recreating it restores use.

On 6/4/20 9:43 AM, Tom Lane wrote:
> Koen De Groote <kdg(dot)dev(at)gmail(dot)com> 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(dot)klaver(at)aklaver(dot)com