sqlpostgresqlpsql

How to create index on several ORDER BY conditions?


On postgresql@12 I have a table with 300 millions rows and first query executes < 1s, but the second is 2 minutes. Why? I created all possible indexes.

First query (< 1s):

SELECT hash FROM _transfers
WHERE (from_='abcd' or to_='abcd') and blockNumber<=12345
ORDER BY blockNumber DESC
LIMIT 1000;

Second query (2 mins):

SELECT hash FROM _transfers
WHERE (from_='abcd' or to_='abcd') and blockNumber<=12345
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 1000;

Indexes:

create index on _transfers(from_);
create index on _transfers(to_);
create index on _transfers(blockNumber);
create index on _transfers(from_, to_, blockNumber);
create index on _transfers(transactionIndex);
create index on _transfers(blockNumber, transactionIndex);
create index on _transfers(from_, to_, blockNumber, transactionIndex);
create index on _transfers(from_, to_) include(blockNumber, transactionIndex);
wallet_hunter=# EXPLAIN SELECT transactionHash
FROM _transfers
WHERE (from_='0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad' or to_='0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad') and blockNumber<=19985646
ORDER BY blockNumber DESC
LIMIT 1000;
QUERY PLAN
.---------------------------------------------------------------------
 Limit  (cost=0.57..531.27 rows=1000 width=71)
   ->  Index Scan Backward using _transfers_blocknumber_idx on _transfers  (cost=0.57..18841537.31 rows=35503334 width=71)
         Index Cond: (blocknumber <= 19985646)
         Filter: (((from_)::text = '0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad'::text) OR ((to_)::text = '0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad'::text))
(4 rows)
wallet_hunter=# EXPLAIN SELECT transactionHash
FROM _transfers
WHERE (from_='0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad' or to_='0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad') and blockNumber<=19985646
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 1000;
QUERY PLAN
.---------------------------------------------------------------------
 Limit  (cost=11241022.67..11241147.06 rows=1000 width=75)
   ->  Gather Merge  (cost=11241022.67..15657190.85 rows=35503335 width=75)
         Workers Planned: 9
         ->  Sort  (cost=11240022.51..11249884.54 rows=3944815 width=75)
               Sort Key: blocknumber DESC, transactionindex
               ->  Parallel Seq Scan on _transfers  (cost=0.00..11023732.55 rows=3944815 width=75)
                     Filter: ((blocknumber <= 19985646) AND (((from_)::text = '0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad'::text) OR ((to_)::text = '0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad'::text)))
 JIT:
   Functions: 5
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(10 rows)

Solution

  • You'll likely need to make an index that specifies the direction, ascending or descending, on each column to match your query.

    https://www.postgresql.org/docs/current/indexes-ordering.html