How to make postgres not use a particular index?

Question

I have the following query:

devapp=>  Explain SELECT DISTINCT "chaindata_tokentransfer"."emitting_contract" FROM "chaindata_tokentransfer" WHERE (("chaindata_tokentransfer"."to_addr" = 100 OR "chaindata_tokentransfer"."from_addr" = 100) AND "chaindata_tokentransfer"."chain_id" = 1 AND "chaindata_tokentransfer"."block_number" >= 10000);
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=29062023.48..29062321.43 rows=8870 width=4)
   ->  Sort  (cost=29062023.48..29062172.45 rows=59591 width=4)
         Sort Key: emitting_contract
         ->  Bitmap Heap Scan on chaindata_tokentransfer  (cost=28822428.06..29057297.07 rows=59591 width=4)
               Recheck Cond: (((to_addr = 100) OR (from_addr = 100)) AND (chain_id = 1) AND (block_number >= 10000))
               ->  BitmapAnd  (cost=28822428.06..28822428.06 rows=59591 width=0)
                     ->  BitmapOr  (cost=4209.94..4209.94 rows=351330 width=0)
                           ->  Bitmap Index Scan on chaindata_tokentransfer_to_addr_284dc4bc  (cost=0.00..1800.73 rows=150953 width=0)
                                 Index Cond: (to_addr = 100)
                           ->  Bitmap Index Scan on chaindata_tokentransfer_from_addr_ef8ecd8c  (cost=0.00..2379.41 rows=200377 width=0)
                                 Index Cond: (from_addr = 100)
                     ->  Bitmap Index Scan on chaindata_tokentransfer_chain_id_block_number_tx_eeeac2a4_idx  (cost=0.00..28818202.98 rows=1315431027 width=0)
                           Index Cond: ((chain_id = 1) AND (block_number >= 10000))
(13 rows)

As you can see, the cost of the last index scan on chaindata_tokentransfer_chain_id_block_number_tx_eeeac2a4_idx is very high. And the query is timing out. If I remove the filter on chain_id and block_number from the query, then the query is executing in a reasonable amount of time. Since this new less constrained query is working, I'd expect even the original more constrained query to work if the index was not there and it was just an additional filter. How to achieve that without deleting the index?

Does this answer your question? [How do I force Postgres to use a particular index?](https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index) — Andy Lester, May 23 '22 at 20:57
I want to keep using the remaining indices for this query and selectively ask it to not use just one index - so I cannot use `enable_seqscan`. From what I got from the link, it seems that that is not possible. :( — Orwell, May 23 '22 at 21:11
`ANALYZE` is run as part of the `autovacuum` process, so either that process is not running enough on that table or has been disabled on the table. — Adrian Klaver, May 24 '22 at 14:20
"that process is not running enough on that table" - How can I test and fix that? — Orwell, May 24 '22 at 15:49
From the docs [pg_stat_all_tables](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW) for vacuum/analyze data. [Autovacuum](https://www.postgresql.org/docs/14/routine-vacuuming.html#AUTOVACUUM). — Adrian Klaver, May 24 '22 at 22:15

score 2 · Accepted Answer · answered May 24 '22 at 02:23

You can probably disable the index by doing some dummy arithmetic on the indexed column.

 ...AND "chaindata_tokentransfer"."chain_id" + 0 = 1...

If you put that into production, make sure to add a code comment on why you are doing such an odd thing.

I'm curious why it chooses to use that index, despite apparently knowing how astonishingly awful it is. If you show the plan for the query with the index disabled, maybe we could figure that out.

If the dummy arithmetic doesn't work, what you could do is start a transaction, drop the index, execute the query (or the just the EXPLAIN of it), then rollback the drop. That is probably not something you want to do often in production (especially since the table will be locked from when the index is dropped until the rollback. Also because you might accidentally commit!) but getting the plan is probably worth doing it once.

FWIW, I set the table on `ANALYZE` yesterday and now even the original query works. But thanks for a general guidance on what things I could have tried. — Orwell, May 24 '22 at 12:43

How to make postgres not use a particular index?

1 Answers1