1

I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.

I have a query which takes almost 17 minutes to run:

select * from public.extrinsics
where chain_id = 1
ORDER BY "extrinsics"."block_number" DESC
limit 10;

I can flip the chain_id to 2 and run the query and it takes less than a second to run.

select count(*) from "extrinsics" where chain_id = 1 = ~ 38M rows
select count(*) from "extrinsics" where chain_id = 2 = ~ 58M rows

I've tried (with no luck):

  • Creating a multi-column index on chain_id & block_number.
  • Creating a multi-column index on chain_id & block_number order DESC.
  • Bumping the statistics on block_number to 10,000 and running ANALYZE
  • Bumping the statistics on chain_id to 10,000 and running ANALYZE

At first I thought it was the query plan, which always resorts to backwards scanning my index on block_number even though I have those other indexes mentioned above but the query plan being wrong doesn't seem to be the issue if chain_id 2 is still fast.

Requested EXPLAIN:

Chain 2 (fast):

"Limit  (cost=0.57..7.76 rows=10 width=829) (actual time=1.563..2.379 rows=10 loops=1)"
"  Buffers: shared read=9"
"  I/O Timings: read=2.310"
"  ->  Index Scan Backward using index_extrinsics_on_block_number on extrinsics  (cost=0.57..41768857.19 rows=58091433 width=829) (actual time=1.561..2.375 rows=10 loops=1)"
"        Filter: (chain_id = 2)"
"        Buffers: shared read=9"
"        I/O Timings: read=2.310"
"Planning Time: 0.636 ms"
"Execution Time: 2.417 ms"

Chain 1 (slow):

"Limit  (cost=0.57..11.60 rows=10 width=829) (actual time=912353.888..912356.009 rows=10 loops=1)"
"  Buffers: shared hit=1872576 read=2079934"
"  I/O Timings: read=890705.882"
"  ->  Index Scan Backward using index_extrinsics_on_block_number on extrinsics  (cost=0.57..41768857.19 rows=37874906 width=829) (actual time=912353.886..912356.003 rows=10 loops=1)"
"        Filter: (chain_id = 1)"
"        Rows Removed by Filter: 10936113"
"        Buffers: shared hit=1872576 read=2079934"
"        I/O Timings: read=890705.882"
"Planning Time: 0.207 ms"
"Execution Time: 912356.134 ms"
-- Table Definition ----------------------------------------------

CREATE TABLE extrinsics (
    id BIGSERIAL PRIMARY KEY,
    block_number bigint NOT NULL,
    extrinsic_index integer,
    timestamp bigint,
    is_signed boolean DEFAULT false,
    signer character varying,
    method character varying,
    section character varying,
    args jsonb,
    extrinsic_hash character varying,
    doc character varying[],
    success boolean DEFAULT false,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL,
    chain_id integer NOT NULL DEFAULT 1
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX extrinsics_pkey ON extrinsics(id int8_ops);
CREATE INDEX index_extrinsics_on_block_number ON extrinsics(block_number int8_ops);
CREATE INDEX index_extrinsics_on_chain_id ON extrinsics(chain_id int4_ops);
CREATE INDEX index_extrinsics_on_signer ON extrinsics(signer text_ops);
CREATE INDEX index_extrinsics_on_signer_and_chain_id ON extrinsics(signer text_ops,chain_id int4_ops);
CREATE UNIQUE INDEX uniq_extrinsics ON extrinsics(block_number int8_ops,extrinsic_index int4_ops,chain_id int4_ops);
CREATE INDEX index_extrinsics_on_chain_id_and_block_number ON extrinsics(chain_id int4_ops,block_number int8_ops);
CREATE INDEX blocks_front_page_index ON extrinsics(chain_id int4_ops,block_number int8_ops DESC);
CREATE INDEX dee_test ON extrinsics(block_number int8_ops DESC NULLS LAST);
0

1 Answer 1

1

The slow plan uses an index on just (block_number), and filters 11 million rows. That's expensive nonsense, and should not occur while a more fitting index on (chain_id, block_number) exists. Unless you have statistics making Postgres (incorrectly) expect it will find 10 rows with chain_id = 1 in the latest rows very quickly.

The fast plan just gets lucky in that the 10 latest rows (with the greatest block_number) all happen to have chain_id = 2. Any other chain_id will perform worse.

Assuming that rows with the greatest block_number are the most recent entries, those are typically not reflected in the column statistics, yet. Postgres makes its guess from previous stats.

So:

  • Do you indeed have said multicolumn index? Preferably on (chain_id, block_number DESC), but if block_number is defined NOT NULL, DESC doesn't matter much.

  • Run ANALYZE extrinsics;. Then test again. Did the problem go away?

Possible Solutions

Partial indexes

If there are only a couple of distinct chain_id, I would create a separate partial index for each (of interest). That should convince Postgres, and the smaller indexes should be faster: 8 bytes of payload per row instead of 16 (with padding). So:

CREATE INDEX ON extrinsics(block_number) WHERE chain_id = 1;
CREATE INDEX ON extrinsics(block_number) WHERE chain_id = 2;
-- more?

Don't do this for more than a few partial indexes, it wouldn't pay.

Separate tables / Partitioning

If there is only a small fixed number, and queries always concern only a single chain_id, consider a separate table for each chain to begin with, or list partitioning. Indexing and queries would fall in line.

Paradoxical intervention

The main problem with this class of bad query plans is that Postgres reckons it can just walk a simple index and filter enough rows quickly to satisfy a very small LIMIT. There are various ways to make Postgres reconsider: improve column statistics, ANALYZE, force certain statistics like n_distinct, planner constants like random_page_cost, remove bloat, VACUUM, more RAM, ... And you should set all of these straight if somethings's wrong in any case.

But a very small LIMIT with only very few distinct chain_id is just very tempting for this simple query plan. A "brute force" fix is to increase the LIMIT by just enough to tip the scales. At some point the estimated cost for this approach probably gets bigger than some other (actually better) plan. And a LIMIT 100 instead of LIMIT 10 may just mean a couple milliseconds more for that other plan. So:

SELECT *
FROM   public.extrinsics
WHERE  chain_id = 1
ORDER  BY block_number DESC NULLS LAST
LIMIT  100;  -- or even more?

DESC NULLS LAST plays nicely with an ASC index. So that can't hurt, even though block_number is defined NOT NULL anyway. (Postgres does not always take that into consideration.) See:

Disable index temporarily

Since you have both - if (!) you at liberty to do so - drop the index on just (block_number) and test the slow query again, while making sure said multicolumn index exists.

Is the good index picked up now? What does EXPLAIN say?

Since dropping and re-creating a huge index is disruptive, blocking and expensive, you might use a hack, as superuser, messing with a system catalog.

DISCLAIMER: you need to know what you are doing or you might break your database (cluster). Wrap it in a transaction and roll back. That makes it less likely you break anything.

BEGIN;

LOCK TABLE public.extrinsics;  -- if any concurrent access is possible

UPDATE pg_catalog.pg_index
SET    indisvalid = false
WHERE  indexrelid = 'public.index_extrinsics_on_block_number'::regclass;

EXPLAIN        -- cheap explain is enough for a first test
SELECT ...  ;  -- your slow query here

-- take note of result!

ROLLBACK;

Should be very fast.
If you committed by mistake, to revert:

UPDATE pg_catalog.pg_index
SET    indisvalid = true
WHERE  indexrelid = 'public.index_extrinsics_on_block_number'::regclass;

While doing all in a single transaction, just use DROP INDEX instead, and roll back. That's only painful if you commit.

Background, quoting the manual on pg_index:

indisvalid bool

If true, the index is currently valid for queries. False means the index is possibly incomplete: it must still be modified by INSERT/UPDATE operations, but it cannot safely be used for queries. If it is unique, the uniqueness property is not guaranteed true either.

And, the manual on CREATE INDEX - "Building Indexes Concurrently":

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an “invalid” index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID:

The above hack exploits this to temporarily "disable" an index. As stated, the index is still maintained at all times, so nothing should break, even under concurrent write load, without exclusive lock. I have used this hack on several occasions. It's just never "safe" to mess with system catalogs at all.

Aside

Rearranging columns would generally save a bit of storage and performance:

CREATE TABLE extrinsics (
  id              bigserial PRIMARY KEY
, block_number    bigint NOT NULL
, created_at      timestamp NOT NULL   -- better timestamptz?
, updated_at      timestamp NOT NULL
, timestamp       bigint
, chain_id        integer NOT NULL DEFAULT 1
, extrinsic_index integer
, success         boolean DEFAULT false
, is_signed       boolean DEFAULT false
, signer          character varying
, method          character varying
, section         character varying
, extrinsic_hash  character varying
, doc             character varying[]
, args            jsonb
);

See:

2
  • So I did the vacuum, analyze, and reindex steps with no luck. I realized that the index on block_number wasn't even needed since my app never queries this table without including a chain_id clause - so I dropped that index and now everything is fast again! I have no idea why that the query planner was doing what it was doing but at least I have a fast app again. Interesting side note: the planner is now using my UNIQ index on block_number ,extrinsic_index , chain_id for this query rather than the chain_id and block_number DESC one
    – Deekor
    Mar 27, 2023 at 4:41
  • @Deekor: "now using my UNIQ index on block_number, extrinsic_index, chain_id for this query rather than the chain_id and block_number DESC one" ... Well, good to hear it's fast, but that index being chosen over the other one makes no sense. :) Mar 27, 2023 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.