How to make postgres not use a particular index?

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?

Answer

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.