PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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)
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    With a query like this, you can create indexes to support the WHERE clause or the ORDER BY clause. You will never be able to create an index that supports both, so make your choice.

    Let’s take the second query as an example.

    To index the WHERE condition, the ideal indexes would be

    CREATE INDEX ON _transfers (_from, blocknumber);
CREATE INDEX ON _transfers (_to, blocknumber);

    To index the ORDER BY clause, you need

    CREATE INDEX ON _transfers (blocknumber, transactionindex);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search