Very expensive index page query

[quentinlesceller]

I've noticed a heavy load on our database simply to display the list of blocks. The query is the following:

SELECT "blockchain_block"."hash", "blockchain_block"."version", "blockchain_block"."height", "blockchain_block"."previous_id", "blockchain_block"."prev_root", "blockchain_block"."timestamp", "blockchain_block"."output_root", "blockchain_block"."range_proof_root", "blockchain_block"."kernel_root", "blockchain_block"."nonce", "blockchain_block"."edge_bits", "blockchain_block"."cuckoo_solution", "blockchain_block"."total_difficulty", "blockchain_block"."secondary_scaling", "blockchain_block"."total_kernel_offset", T2."hash", T2."version", T2."height", T2."previous_id", T2."prev_root", T2."timestamp", T2."output_root", T2."range_proof_root", T2."kernel_root", T2."nonce", T2."edge_bits", T2."cuckoo_solution", T2."total_difficulty", T2."secondary_scaling", T2."total_kernel_offset" FROM "blockchain_block" LEFT OUTER JOIN "blockchain_block" T2 ON ("blockchain_block"."previous_id" = T2."hash") ORDER BY "blockchain_block"."timestamp" DESC  LIMIT 20;

This query is extremely expensive and very slow even on a powerful db (like 10s to compute). I am wondering whether we are missing an index somewhere.

Here is the table description:

explorer=> \d+ blockchain_block
                                             Table "public.blockchain_block"
       Column        |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
---------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 hash                | character varying(64)    |           | not null |         | extended |              |
 version             | integer                  |           | not null |         | plain    |              |
 height              | integer                  |           | not null |         | plain    |              |
 timestamp           | timestamp with time zone |           | not null |         | plain    |              |
 output_root         | character varying(64)    |           | not null |         | extended |              |
 range_proof_root    | character varying(64)    |           | not null |         | extended |              |
 kernel_root         | character varying(64)    |           | not null |         | extended |              |
 nonce               | text                     |           | not null |         | extended |              |
 total_difficulty    | bigint                   |           | not null |         | plain    |              |
 previous_id         | character varying(64)    |           |          |         | extended |              |
 total_kernel_offset | character varying(64)    |           | not null |         | extended |              |
 edge_bits           | integer                  |           | not null |         | plain    |              |
 cuckoo_solution     | bigint[]                 |           | not null |         | extended |              |
 prev_root           | character varying(64)    |           | not null |         | extended |              |
 secondary_scaling   | integer                  |           | not null |         | plain    |              |
Indexes:
    "blockchain_block_pkey" PRIMARY KEY, btree (hash)
    "blockchain_block_hash_7b6393e6_like" btree (hash varchar_pattern_ops)
    "blockchain_block_height_bf2470f4" btree (height)
    "blockchain_block_previous_id_ead75ff5" btree (previous_id)
    "blockchain_block_previous_id_ead75ff5_like" btree (previous_id varchar_pattern_ops)
Foreign-key constraints:
    "blockchain_block_previous_id_ead75ff5_fk_blockchain_block_hash" FOREIGN KEY (previous_id) REFERENCES blockchain_block(hash) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "blockchain_block" CONSTRAINT "blockchain_block_previous_id_ead75ff5_fk_blockchain_block_hash" FOREIGN KEY (previous_id) REFERENCES blockchain_block(hash) DEFERRABLE INITIALLY DEFERRED
    TABLE "blockchain_input" CONSTRAINT "blockchain_input_block_id_0d78b8c6_fk_blockchain_block_hash" FOREIGN KEY (block_id) REFERENCES blockchain_block(hash) DEFERRABLE INITIALLY DEFERRED
    TABLE "blockchain_kernel" CONSTRAINT "blockchain_kernel_block_id_8c53f550_fk_blockchain_block_hash" FOREIGN KEY (block_id) REFERENCES blockchain_block(hash) DEFERRABLE INITIALLY DEFERRED
    TABLE "blockchain_output" CONSTRAINT "blockchain_output_block_id_2a940961_fk_blockchain_block_hash" FOREIGN KEY (block_id) REFERENCES blockchain_block(hash) DEFERRABLE INITIALLY DEFERRED

@hendi any idea?

[hendi]

That's strange. On my cheap box running grinexplorer.net that query takes "just" 2s. Can you please do an EXPLAIN ANALYZE for me? I'm curious to see how our two execution plans differ.

It's indeed missing an index though, please do:

CREATE INDEX ON blockchain_block (timestamp); ANALYZE blockchain_block;

and then do the EXPLAIN ANALYZE again. That new index gave me a speedup of ~10.

Another thing that's not optimal is that hash, the pkey, is a text and is used for the lookup in the JOIN statement. We should consider two changes: changing the hash to bytea and/or use a numeric pkey (instead of the hash). I'll open issue for these once we've looked at your specific issue.

[quentinlesceller]

Okay massive speedup with the new index. Thanks @hendi !!!