From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Mats Olsen <mats(at)duneanalytics(dot)com> |
Cc: | Sebastian Dressler <sebastian(at)swarm64(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query Performance / Planner estimate off |
Date: | 2020-10-22 13:48:26 |
Message-ID: | 20201022134826.GV9241@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mats Olsen | 2020-10-23 12:16:35 | Re: Query Performance / Planner estimate off |
Previous Message | Mats Olsen | 2020-10-22 07:36:03 | Re: Query Performance / Planner estimate off |