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 ? 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 > > 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). -- Justin
