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

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(dot)klaver(at)aklaver(dot)com