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 1kUv1j-0001dO-19 for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 17:02:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kUv1h-00075F-Ug for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 17:02:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kUv1h-000758-MR for pgsql-performance@lists.postgresql.org; Tue, 20 Oct 2020 17:02:21 +0000 Received: from mail-lj1-x233.google.com ([2a00:1450:4864:20::233]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kUv1e-0003Jf-Lg for pgsql-performance@postgresql.org; Tue, 20 Oct 2020 17:02:21 +0000 Received: by mail-lj1-x233.google.com with SMTP id m16so2808688ljo.6 for ; Tue, 20 Oct 2020 10:02:18 -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=KbuU/Zt+P+ECjMSyV949/2uc/Rd+QKBrj79BdyBTU0E=; b=gL4QDl44WViYR0yM/QZ3Mhojm440p5+F/Jlv//JtbWw8uPKNPxYJ2bEJl4woc3YJvp 0aOLjsjeHfL+DtrcGeGzP58C0QJM3+sNVbAHJ4T2R3n5qmjrRvOlnJuAxMK7jc5M1Si8 7akLq3gfDZbpc9KTbA/9dQAloZkM8h5lMhbXce0w0SYfeOtQJhYTT1/tK1xDfvPjQQq6 syJaXL/7XSS3t7W+sQdSv0e8xwFgVCH27ix+oKsHwwJfxPh51z+bUNMYG/Z+QRDoSdA1 q24y/QiP/2bvf6aovykLOpehVKysqtDtq+qFhCBBnVPKZnyDWO4ajKobtp7+EzlUE6ln dtGQ== 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=KbuU/Zt+P+ECjMSyV949/2uc/Rd+QKBrj79BdyBTU0E=; b=FERgYcBPhO/J04aJqwOZa8Qev0WLADU/hzUjWuRRNXCEXn41RU/rqPaghC3/8em9w/ 0VgQihJ5UB4kJj+oQl3kNWwDRc79BGr+wgR5Mti9p+hfolmEzy7GPoiK3TY0VWXbnGZs TGpsW+3cxHhM4DBVfI++xkEQElAQmL/D+K1x5yucZj1bt6eTKqE958RdIeHVXQ/0KFed vli1noWzzHbIaYLGxy/O+qtf6mK2xxxkt41BZ2nGSwccMvFjtrzTQNoLPWlBwmPy6Qk0 TDymN3n6/O2EUBDj0QdHFPooh8GxTs2nB+pNH8WX2SH+FtUe3m/i/1Gx7VhneWmcP9J/ +1dw== X-Gm-Message-State: AOAM5307pQkZOCss6tu1fQmCY4VNvEdhP5o5iFABE+JWdNB0A8RBcRRj 3w26lcYLAqRczBL7Mbt9ttgOuG4TDu5fF+Lm X-Google-Smtp-Source: ABdhPJzz4wtVhAepoNNIZYA3aFfMtlqTLm0RsdxfL2JyOOvs/rbdyLOtx+J3OdSpe/YzTD4z9Piv1g== X-Received: by 2002:a2e:3e1a:: with SMTP id l26mr1697881lja.63.1603213336901; Tue, 20 Oct 2020 10:02:16 -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 l25sm380726lfc.152.2020.10.20.10.02.15 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 20 Oct 2020 10:02:16 -0700 (PDT) Subject: Re: Query Performance / Planner estimate off To: Sushant Pawar Cc: pgsql-performance@postgresql.org References: From: Mats Olsen Message-ID: Date: Tue, 20 Oct 2020 19:02:09 +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="------------4493CF7F492FC9DCBA938153" 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. --------------4493CF7F492FC9DCBA938153 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 10/20/20 3:40 PM, Sushant Pawar wrote: > Looping in the main group ID. > > Regards > Sushant > > On Tue, Oct 20, 2020 at 6:49 PM Sushant Pawar > wrote: > > > On Tue, Oct 20, 2020 at 3:08 PM Mats Julian Olsen > > wrote: > > Dear Postgres community, > > I'm looking for some help to manage queries against two large > tables. > > Context: > We run a relatively large postgresql instance (5TB, 32 vCPU, > 120GB RAM) with a hybrid transactional/analytical workload. > Data is written in batches every 15 seconds or so, and the all > queryable tables are append-only (we never update or delete). > Our users can run analytical queries on top of these tables. > > We recently came across a series of troublesome queries one of > which I'll dive into here. > > Please see the following gist for both the query we run and > the \d+ output: > https://gist.github.com/mewwts/9f11ae5e6a5951593b8999559f5418cf > . > > > The tables in question are: > - `ethereum.transactions`: 833M rows, partitioned, 171M rows > after WHERE > - `uniswap_v2."Pair_evt_Swap": 12M rows, not partitioned, 12M > rows after WHERE > > The crux of our issue is that the query planner chooses a > nested loop join for this query. Essentially making this query > (and other queries) take a very long time to complete. In > contrast, by toggling `enable_nestloop` and `enable_seqscan` > off we can take the total runtime down from 16 minutes to 2 > minutes. > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR > > 2) enable_nestloop=off (4 min): > https://explain.depesz.com/s/buKK > > 3) enable_nestloop=off; enable_seqscan=off (2 min): > https://explain.depesz.com/s/0WXx > > > > The cost of a query while using the default Vanila plan is very > less compared to the 3rd plan with nested loop and seqscan  being > set to off.  As the JIT is enabled, it seems the planner tries to > select the plan with the least cost and going for the plan which > is taking more time of execution. Can you try running this query > with JIT=off in the session and see if it selects the plan with > the least time for execution? > Thank you for your reply. Here's the result using set jit=off; https://explain.depesz.com/s/rpKc. It's essentially the same plan as the initial one. > > How can I get Postgres not to loop over 12M rows? > > Let me know if there is anything I left out here that would be > useful for further debugging. > > -- > Regards > >     Sushant > --------------4493CF7F492FC9DCBA938153 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit


On 10/20/20 3:40 PM, Sushant Pawar wrote:
Looping in the main group ID.

Regards
Sushant

On Tue, Oct 20, 2020 at 6:49 PM Sushant Pawar <sushantxp@gmail.com> wrote:

On Tue, Oct 20, 2020 at 3:08 PM Mats Julian Olsen <mats@duneanalytics.com> wrote:
Dear Postgres community,

I'm looking for some help to manage queries against two large tables.

Context:
We run a relatively large postgresql instance (5TB, 32 vCPU, 120GB RAM) with a hybrid transactional/analytical workload. Data is written in batches every 15 seconds or so, and the all queryable tables are append-only (we never update or delete). Our users can run analytical queries on top of these tables.

We recently came across a series of troublesome queries one of which I'll dive into here.

Please see the following gist for both the query we run and the \d+ output: https://gist.github.com/mewwts/9f11ae5e6a5951593b8999559f5418cf.

The tables in question are:
- `ethereum.transactions`: 833M rows, partitioned, 171M rows after WHERE
- `uniswap_v2."Pair_evt_Swap": 12M rows, not partitioned, 12M rows after WHERE

The crux of our issue is that the query planner chooses a nested loop join for this query. Essentially making this query (and other queries) take a very long time to complete. In contrast, by toggling `enable_nestloop` and `enable_seqscan` off we can take the total runtime down from 16 minutes to 2 minutes.

1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
3) enable_nestloop=off; enable_seqscan=off (2 min): https://explain.depesz.com/s/0WXx 

The cost of a query while using the default Vanila plan is very less compared to the 3rd plan with nested loop and seqscan  being set to off.  As the JIT is enabled, it seems the planner tries to select the plan with the least cost and going for the plan which is taking more time of execution. Can you try running this query with JIT=off in the session and see if it selects the plan with the least time for execution?

Thank you for your reply. Here's the result using set jit=off; https://explain.depesz.com/s/rpKc. It's essentially the same plan as the initial one.


How can I get Postgres not to loop over 12M rows?

Let me know if there is anything I left out here that would be useful for further debugging.

--
Regards
    Sushant 
--------------4493CF7F492FC9DCBA938153--