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

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