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

[Koen De Groote <kdg.dev@xxxxxxxxx>]

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.

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