Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kUvfi-0004DI-FE for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 17:43:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kUvfg-0008Ps-3N for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 17:43:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kUvff-0008Pl-OL for pgsql-performance@lists.postgresql.org; Tue, 20 Oct 2020 17:43:39 +0000 Received: from mail-lj1-x22f.google.com ([2a00:1450:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kUvfb-0003E0-Vz for pgsql-performance@postgresql.org; Tue, 20 Oct 2020 17:43:38 +0000 Received: by mail-lj1-x22f.google.com with SMTP id y16so2971565ljk.1 for ; Tue, 20 Oct 2020 10:43:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=duneanalytics-com.20150623.gappssmtp.com; s=20150623; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=3iyPLqEmepkzXOI0oL6rxLz/X34cB9YsrBsfNFLhQZo=; b=fM7SYlxmg95xcPRLbVLMh107kWrJ0UqE4o1282UUQjHcSbT3Rizfd3I/e5beFZ7eXJ jI4EPoLQugeKsEEnrShCKqYFKUiUqc/NDG6yafKUMU6P/u6ojbbB7884jxJuAQ/fFANu s4ZSbMrUAlSvGyJe0KHpyMEeEZrOOIaGfFsbEhaSwBeM5GANPe9uT95SQqlNCYSiKdad UIIwyWa1IYCSWSQGJ2ugOCO0Y4mkA0V8c2DkCF2SQv8lAl91b1XlEyx1E7emxEP/SnAh bTzq+nu+Zei74zNHXL8eGnXDFVJaYb1673MQJYjdmHCqAQvKfiLO3RpIi1ryhptAoqlP Y9uA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=3iyPLqEmepkzXOI0oL6rxLz/X34cB9YsrBsfNFLhQZo=; b=KAGLIpD+Arb/clLLtvvGUPE+hXuLQsIvDZU3nH/4Q78q720Ovfwk0G+n20W7nxe1Qi Eq7T/Xa1tW100GR/uLMdRA8Kk288e6u+3W40erlf8mOjP6ut7QMmDURLLmuzX0IBxpTH ZG5bifAykoCnDZbbQs1u6En3ZkuOHeFPPPNoTR0utn/itJFVSjhCpL7LM0TTOAWx+eLk 5xitItGkSKBu2pk32R4kzMmP8+UCp+unSCXdy77hYSzggXk0nG97v0fWCejVewSWhrUc mnpnWfm/9pmC6uTNMxUS6lGQl+Dedgh90GiaItlNaqbrxvxWSmI8Owid/oM5HA7cGjyg hwRQ== X-Gm-Message-State: AOAM531Tb97N5pEHQn+3iwetAUkXqCQ8RpQFthZPl2SNwFyYCs2+JfMc xLH3VhRCzr+nN8IJ2p5YDswNrDvu0+WqAK5N X-Google-Smtp-Source: ABdhPJxRTpFHZO2Uf+qXK3MGKSJWqf9NayW95ny5gPG4zxERJOwRbINA4Na+qCGvAkrPo0iBjwgpjw== X-Received: by 2002:a2e:3112:: with SMTP id x18mr1697815ljx.149.1603215813688; Tue, 20 Oct 2020 10:43:33 -0700 (PDT) Received: from [192.168.1.181] (host-37-191-238-241.lynet.no. [37.191.238.241]) by smtp.gmail.com with ESMTPSA id u26sm500153ljo.40.2020.10.20.10.43.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 20 Oct 2020 10:43:32 -0700 (PDT) Subject: Re: Query Performance / Planner estimate off To: Victor Yegorov Cc: pgsql-performance@postgresql.org References: <225f4817-ca74-a595-8ff4-58c6a591578b@duneanalytics.com> From: Mats Olsen Message-ID: Date: Tue, 20 Oct 2020 19:43:27 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.3.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------D78575790A6EEA5330C10D04" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------D78575790A6EEA5330C10D04 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 10/20/20 6:51 PM, Victor Yegorov wrote: > вт, 20 окт. 2020 г. в 16:50, Mats Olsen >: > > On 10/20/20 3:04 PM, Victor Yegorov wrote: > >> вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen >> >: >> >> I'm looking for some help to manage queries against two large >> tables. >> >> >> Can you tell the version you're running currently and the output >> of this query, please? >> >>     select name,setting,source from pg_settings where source not >> in ('default','override'); >> > Running "PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on > x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 > 20191008, 64-bit" > > Updated the gist to include the results forom pg_settings. Here's > the direct link > https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/e5deebbbb48680e04570bec4e9a816fa009da34f/pg_settings > > > It looks like indexes currently chosen by the planner don't quite fit > your query. > > I would create the following index (if it's possible to update schema): >    ON "uniswap_v2.Pair_evt_Mint" (evt_tx_hash, evt_block_time) I'll try to add this. > > Same for the second table, looks like >   ON "ethereum.transactions" (hash, block_time) > is a better fit for your query. In fact, I do not think > `transactions_block_number_time` index is used frequently, 'cos second > column of the index is a partitioning key. I'll see if I can add it. This table is huge so normally we only make changes to these when we redeploy the database. > > Currently planner wants to go via indexes 'cos you've made random > access really cheap compared to sequential one (and your findings > shows this). > Perhaps on a NVMe disks this could work, but in your case you need to > find the real bottleneck (therefore I asked for buffers). > > I would set `random_page_cost` to a 2.5 at least with your numbers. > Also, I would check DB and indexes for bloat (just a guess now, 'cos > your plans miss buffers figures) Yeah, 1.1 seems way to low. Here's the output of the explain (analyze, buffers, settings) you asked for: vanilla: https://explain.depesz.com/s/Ktrd set enable_nestloop=off: https://explain.depesz.com/s/mvSD set enable_nestloop=off; set enable_seqscan=off: https://explain.depesz.com/s/XIDo > > > -- > Victor Yegorov --------------D78575790A6EEA5330C10D04 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit


On 10/20/20 6:51 PM, Victor Yegorov wrote:
вт, 20 окт. 2020 г. в 16:50, Mats Olsen <mats@duneanalytics.com>:

On 10/20/20 3:04 PM, Victor Yegorov wrote:

вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen <mats@duneanalytics.com>:
I'm looking for some help to manage queries against two large tables.

Can you tell the version you're running currently and the output of this query, please?

    select name,setting,source from pg_settings where source not in ('default','override'); 

Running "PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit"

Updated the gist to include the results forom pg_settings. Here's the direct link https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/e5deebbbb48680e04570bec4e9a816fa009da34f/pg_settings

It looks like indexes currently chosen by the planner don't quite fit your query.

I would create the following index (if it's possible to update schema):
   ON "uniswap_v2.Pair_evt_Mint" (evt_tx_hash, evt_block_time)
I'll try to add this.

Same for the second table, looks like
  ON "ethereum.transactions" (hash, block_time)
is a better fit for your query. In fact, I do not think `transactions_block_number_time` index is used frequently, 'cos second column of the index is a partitioning key.
I'll see if I can add it. This table is huge so normally we only make changes to these when we redeploy the database.

Currently planner wants to go via indexes 'cos you've made random access really cheap compared to sequential one (and your findings shows this).
Perhaps on a NVMe disks this could work, but in your case you need to find the real bottleneck (therefore I asked for buffers).

I would set `random_page_cost` to a 2.5 at least with your numbers. Also, I would check DB and indexes for bloat (just a guess now, 'cos your plans miss buffers figures)

Yeah, 1.1 seems way to low.

Here's the output of the explain (analyze, buffers, settings) you asked for:

vanilla: https://explain.depesz.com/s/Ktrd

set enable_nestloop=off: https://explain.depesz.com/s/mvSD

set enable_nestloop=off; set enable_seqscan=off: https://explain.depesz.com/s/XIDo




--
Victor Yegorov
--------------D78575790A6EEA5330C10D04--