Thanks for your response Justin.
On 10/22/20 3:48 PM, Justin Pryzby wrote:
On Thu, Oct 22, 2020 at 09:36:03AM +0200, Mats Olsen wrote:
On 10/22/20 8:37 AM, Justin Pryzby wrote:
These look redundant (which doesn't matter for this the query):
Partition key: RANGE (block_number)
Indexes:
"transactions_block_number_btree" btree (block_number DESC)
"transactions_block_number_hash_key" UNIQUE CONSTRAINT, btree (block_number, hash)
"transactions_block_number_time" btree (hash, block_number)
Maybe that would be an index just on "hash", which might help here.
Possibly you'd want to try to use a BRIN index on timestamp (or maybe
block_number?).
Yeah this could be a good idea, but the size of this table doesn't let me
add any indexes while it's online. I'll revisit these the next time we
redeploy the database.
Why not CREATE INDEX CONCURRENTLY ?
We could, but it would take forever on the `ethereum.transactions` table.
It seems to me you could add BRIN on all correlated indexes. It's nearly free.
0.102922715 | Pair_evt_Mint | evt_block_time | f | 0 | -0.56466025 | 10000 | 10001 | 0.964666
0.06872191 | Pair_evt_Mint | evt_block_time | f | 0 | -0.8379525 | 500 | 501 | 0.99982
0.06872191 | Pair_evt_Mint | evt_block_number | f | 0 | -0.8379525 | 500 | 501 | 0.99982
0.032878816 | Pair_evt_Mint | evt_block_number | f | 0 | -0.56466025 | 2500 | 2501 | 0.964666
Agreed, could try to add BRIN's on these.
Maybe you'd want to VACUUM the table to allow index-only scan on the hash
columns ?
Did you try it ? I think this could be a big win.
Since it's append-only, autovacuum won't hit it (until you upgrade to pg13).
I vacuumed the uniswap_v2."Pair_evt_Mint", but still getting the same
plan, unfortunately.
