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)
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