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 1kUulj-0000YQ-2Z for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 16:45:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kUulh-0004L5-Ua for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 16:45:49 +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 1kUulh-0004JF-Gt for pgsql-performance@lists.postgresql.org; Tue, 20 Oct 2020 16:45:49 +0000 Received: from mail-lj1-x22d.google.com ([2a00:1450:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kUule-0003B1-Cu for pgsql-performance@postgresql.org; Tue, 20 Oct 2020 16:45:48 +0000 Received: by mail-lj1-x22d.google.com with SMTP id m20so2756966ljj.5 for ; Tue, 20 Oct 2020 09:45:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=duneanalytics-com.20150623.gappssmtp.com; s=20150623; h=subject:from:to:references:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=nvBJSn/Rhu0lzQPfRzdYil0i+bHztVS7ywSfMy9Tsf8=; b=YMbwdnsz9oA7mOtO4Nnjj658gBi1JaUVndGd+oXbbYqjydkG9skc0L+WT4DIA6ZcKG SV4ZNcn2iDEwUV97RfojPaJkSob9fKi0tSyJeWoX0fpFIrFgiQ3d9YX6wUiUHngWalx1 jq1Pa5CNtGotdwdOlxw+6omqUgAXYwx/9p5BPHMEM3jtmwcCT01UFjpQKWU6NVXg9H3L 7dq8EgFR33AFafDI9EcHmnMR7PzyHqYJI8ho8xxR7htP+qz3nCr6SjQtbmFCQbbhO2+X nHMPeqK/vnFYYj+0zpKkSA7ApR1mrsFZRylgZ9thwy+wbpBnKcDOW77/HvB073+N618s Bo3A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:from:to:references:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=nvBJSn/Rhu0lzQPfRzdYil0i+bHztVS7ywSfMy9Tsf8=; b=m4hXTnhiP+lCmbAF6sbDLSL8cKrWrmHqfk3XDOFCOys+c0nVDvX2YCEe7Ono35hb3l E/ooHt+5VOZskZlfaJX1ytLOEUBjK6li94Wzm7TDJ5PKde/UWl98gHWaj+NDJQdvK/8M GwOFRAhtHr/wIPxGeTqDoLWEfDfgJysX7hp8eTQ2oDhWni5K555s8K72a1mQLF6t5Aqg 8s0qtz3dtIjGokb7arnmmDkYvUq6EQHzhNgXHywcxLj0i9IY2HYz5VvP0I9IA11cdTTF qOW+wOcJVkSHxRNdz+/lcxUPiCxFxhwkBDyI46i0v+IDO4YT2mQJLQHyWT1Zv5vCwFg4 9F3w== X-Gm-Message-State: AOAM530RbRuB+rO01fkOboT7lhirHdyugpnk+RxwlWSuRN4a0Oj7dh/9 1TYEJ7D8vsn6/s3vDvjBRAqlOMOJxTDenpuB X-Google-Smtp-Source: ABdhPJw324y/GoqsryI+j/GcFPWVEX1VXktJlAmuwngFan/i/llgsEB482vbX/0ZSdFlvQSKD1FfRA== X-Received: by 2002:a2e:7a0a:: with SMTP id v10mr1475083ljc.188.1603212344694; Tue, 20 Oct 2020 09:45:44 -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 e3sm382203lfs.248.2020.10.20.09.45.42 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 20 Oct 2020 09:45:43 -0700 (PDT) Subject: Re: Query Performance / Planner estimate off From: Mats Olsen To: pgsql-performance@postgresql.org References: Message-ID: <60498453-2278-5f62-7ff6-343eea7bfd3b@duneanalytics.com> Date: Tue, 20 Oct 2020 18:45:36 +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="------------F5D9AB0F7A6D009D0DFD8A1F" 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. --------------F5D9AB0F7A6D009D0DFD8A1F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 10/20/20 11:37 AM, 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 query plans I submitted was querying the table `uniswap_v2."Pair_evt_Mint"`which has 560k rows before and after WHERE. Also not partitioned. Apologies for the inconsistency, but as I mentioned the same performance problem holds when using `uniswap_v2."Pair_evt_Swap" (even worse due to it's size). > > 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 > > 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. > > -- > Mats > CTO @ Dune Analytics > We're hiring: https://careers.duneanalytics.com > --------------F5D9AB0F7A6D009D0DFD8A1F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 10/20/20 11:37 AM, 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 query plans I submitted was querying the table `uniswap_v2."Pair_evt_Mint"`which has 560k rows before and after WHERE. Also not partitioned. Apologies for the inconsistency, but as I mentioned the same performance problem holds when using `uniswap_v2."Pair_evt_Swap" (even worse due to it's size).


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 

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.

--
Mats
CTO @ Dune Analytics
--------------F5D9AB0F7A6D009D0DFD8A1F--