Re: Index no longer being used, destroying and recreating it restores use. - Mailing list pgsql-general

I've collected all relevant info(I think so at least) and put it here:

The table in question is used to keep filepath data, of files on a harddrive.
The query in question is used to retrieve items which should be backed up, but have not yet been.

The relevant columns of the table:

                                                    Table "public.item"
               Column               |            Type             | Collation | Nullable |                   Default                    
------------------------------------+-----------------------------+-----------+----------+----------------------------------------------
 id                                 | bigint                      |           | not null | nextval('item_id_seq'::regclass)
 shouldbebackedup                   | boolean                     |           | not null | true
 backupperformed                    | boolean                     |           | not null | false
 itemCreated                        | timestamp without time zone |           |          | now()
 filepath                           | text                        |           |          |


The existing index, which no longer gets used:
"index_in_question" btree (shouldbebackedup, backupperformed, itemCreated, filepath) WHERE shouldbebackedup = true AND backupperformed = false

The new index, made out of the exact same columns and conditions, get used immediately after creation:
CREATE INDEX CONCURRENTLY index_test ON item USING btree (shouldbebackedup, backupperformed, itemCreated, filepath) WHERE shouldbebackedup = true AND backupperformed = false;


The query in question will look something like this:
select * from item where shouldbebackedup=true and itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by filepath asc, id asc limit 100 offset 10400;

Having done a count, there are around 13000 items here, without the offset and limit.
That being said, the amount is entirely dependant on what was added on a previous day.


I tried creating an extended statistic, like this, but it had no effect:
CREATE STATISTICS s1 (dependencies) ON shouldbebackedup, backupperformed FROM item;

Settings from the conf file I think are related:

shared_buffers = 1024MB
effective_cache_size = 2048MB
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 32MB

Finally, I state again that this database gets a nightly "vacuum analyze".

My thanks for looking at this and any suggestions one might have.

Regards,
Koen