Date: Thu, 22 Oct 2020 08:48:26 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mats Olsen <mats@duneanalytics.com>
Cc: Sebastian Dressler <sebastian@swarm64.com>,
	pgsql-performance@postgresql.org
Subject: Re: Query Performance / Planner estimate off
Message-ID: <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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <7f586824-0d53-0f84-15f1-f460dbc414b5@duneanalytics.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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