near / near-indexer-for-explorer

Watch NEAR network and store all the data from NEAR blockchain to PostgreSQL database
https://near-indexers.io/docs/projects/near-indexer-for-explorer
GNU General Public License v3.0
124 stars 56 forks source link

feat: introduced an index on transactions to speed up sorting by block_timestamp and index_in_chunk #139

Closed telezhnaya closed 3 years ago

telezhnaya commented 3 years ago

Resolves #135

To apply it to mainnet, we should use

CREATE INDEX CONCURRENTLY transactions_sorting_idx ON transactions(block_timestamp, index_in_chunk);

(We never put CONCURRENTLY to migration files, I decided that I should be consistent here)

Applying it to 35.233.39.106 (it's copy of mainnet with data till 10th of May 2021) took 75 seconds. Select from @frol works in a moment. Important: columns block_timestamp, index_in_chunkshould always go in this order, and we should specify desc for both or for no columns. That anyway sounds logical.

select pg_size_pretty(pg_table_size('transactions_sorting_idx'));

For 14M lines, it took 550 MB of storage. Mainnet has 17M lines, so hopefully it should not be so far from these numbers.

Can I apply it to mainnet?