From: Mats Olsen <mats@duneanalytics.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Sebastian Dressler <sebastian@swarm64.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance / Planner estimate off
Date: Fri, 23 Oct 2020 14:16:35 +0200
Message-ID: <e5a68610-76db-28c2-9377-06ec1cb48918@duneanalytics.com> (raw)
In-Reply-To: <20201022134826.GV9241@telsasoft.com>
References: <CAARtqpGxzna+jj3LYNTacHxbC03-KdBs7XeQUaqwEkUJ+5QMbw@mail.gmail.com>
<FE09A620-390B-43DC-B73A-183790581443@swarm64.com>
<61c9fd35-bdb8-3fe6-bda0-81c7e6a9eaf8@duneanalytics.com>
<20201022063701.GU9241@telsasoft.com>
<7f586824-0d53-0f84-15f1-f460dbc414b5@duneanalytics.com>
<20201022134826.GV9241@telsasoft.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mats@duneanalytics.com, pryzby@telsasoft.com, sebastian@swarm64.com
Subject: Re: Query Performance / Planner estimate off
In-Reply-To: <e5a68610-76db-28c2-9377-06ec1cb48918@duneanalytics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox