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@xxxxxxxxxxx
