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

On 6/4/20 8:37 AM, Koen De Groote wrote:
> Greetings,
> 
> The following is using version 11.2 of PostgreSQL.
> 
> 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.

Did you ANALYZE the table at that point?

> 
> 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 the exact same query and the index is identical in composition. Yet 
> after a while the database stops using it. I'd like to find out why that 
> is and how to prevent it.
> 
> 
> Also, I'm assuming this is the correct list for such a question?
> 
> 
> In the past, I had asked this somewhere else, but then no longer had 
> time to spend on it: https://dba.stackexchange.com/questions/264237/
> 
> Some data I gathered then:
> 
>  1. Size of the index not being used is 101MB.
>  2. Size of the index being used is 16MB.
> 
> The query takes the form of:
> 
> "select * from myTable where bool1 = true and bool2 = false and 
> timestamp <= ('timestampField'::timestamp without time zone) order by 
> stringField asc, id asc limit 100 offset 30000;"
> 
> 30000 is an example value.
> #
> 
> Here is the "explain analyze" for index used: 
> https://explain.depesz.com/s/H5X9y
> 
> #
> 
> Here is the "explain analyze" for index not used: 
> https://explain.depesz.com/s/n6bP
> 
> 
> And I'm frankly stumped.An index growing from 16MB to 101MB isn't that 
> big of an increase, I would think? Is that the reason it's no longer 
> being used? Or is something else going on here?
> 
> The entire database, in which this table belongs, undergoes a "vacuum 
> analyze" every single night, which takes about 8 minutes. Do I perhaps 
> need to do something additional in terms of cleanup/maintenance?
> 
> I've tried altering statistics, to very large values even, but no 
> changes there either.
> 
> Any help or suggestion would be appreciated.
> 
> Kind regards,
> Koen De Groote
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com