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);
11
  • Either of those indexes should have worked. To figure out why they didn't, please share the EXPLAIN (ANALYZE, BUFFERS) for both of the used chain_id.
    – jjanes
    Mar 26 at 20:10
  • With a multicolumn index on (chain_id, block_number), the query should be a matter of milliseconds, not seconds or even minutes. Something's completely off here, or some colossal misunderstanding ... Please provide information as detailed here: dba.meta.stackexchange.com/a/3299/3684 Mar 26 at 20:12
  • @ErwinBrandstetter ive updated the question with the EXPLAIN
    – Deekor
    Mar 26 at 20:44
  • @jjanes Ive updated the question
    – Deekor
    Mar 26 at 20:44
  • No images please. Provide plain text output. Mar 26 at 20:49

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?

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:

Experimental Debugging

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 we roll back anyway, we might just use DROP INDEX. That's only painful we you don't.

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:

10
  • Yes i have that exact index, yes I've run analyze, no it didn't help. I even bumped statistics for block_number and chain_id to 10,000
    – Deekor
    Mar 26 at 21:12
  • Im curious why the IO timings are crazy on the slow query..
    – Deekor
    Mar 26 at 21:13
  • If you have that index, and recently ran ANALYZE, then something is seriously broken in your DB. I would start by making sure there is a working, recent backup, then investigate what's broken. Step 1: VACUUM public.extrinsics; Then REINDEX public.extrinsics; Or there is a lingering misunderstanding, like a mixup of connections, schemas, table names ... You didn't disclose all the requested debug information, most importantly exact CREATE TABLE and CREATE INDEX statements. Mar 26 at 21:19
  • @Deekor: I added some experimental stuff - if you are at liberty to try that. Mar 26 at 21:59
  • Im waiting for the reindex to run, and then I will try some of those options. Ive added the table and index definitions to the question. Thanks for your help.
    – Deekor
    Mar 26 at 22:11

Your Answer

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

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