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 1kVTz8-0004kh-Cr for pgsql-performance@arkaria.postgresql.org; Thu, 22 Oct 2020 06:22:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kVTz5-0003I0-Vx for pgsql-performance@arkaria.postgresql.org; Thu, 22 Oct 2020 06:21:59 +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 1kVTz5-0003Ht-Gc for pgsql-performance@lists.postgresql.org; Thu, 22 Oct 2020 06:21:59 +0000 Received: from mail-lj1-x244.google.com ([2a00:1450:4864:20::244]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kVTz2-0007sj-AI for pgsql-performance@postgresql.org; Thu, 22 Oct 2020 06:21:58 +0000 Received: by mail-lj1-x244.google.com with SMTP id x16so697420ljh.2 for ; Wed, 21 Oct 2020 23:21:55 -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=VFyVcJ9bwSwZw3/YChw/9Yrcfiq5kVXYDTwSIgMZqLA=; b=eubRmPzNdb7F+gOg8fBeapspX972fJ9TqSAolydLm0q+iAeAFp4Lfps2+4YfAmtHc6 phrL1erSNWz9AxXTqnqI3vPnvkyydmhM1x0R4iyBP7d51GORB1/p6ek1uMXFDuJxmn0S bOFK96TLFdude0ArCDZRvmy7s7706F5fvJRVWXfMY6s2y8EkYrsKWrWlQBXoiu17vXhW KqUm49JOK5cKEq23JHBAaJOs1uYY+j1sz1BUxG3CclJ9Jf+o7e4qDJrjphcDL0z6KiHf ddKMJ84wwjBeu9MpXTRyc8baVzEtHY4ZqRod+N8z7w/MCPmujAse/Sq+M3TI0ejr0PxQ iuvw== 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=VFyVcJ9bwSwZw3/YChw/9Yrcfiq5kVXYDTwSIgMZqLA=; b=eEQOwsDJQYS2oUe5QH753tovBhh+qYCuSmmt8Rx/L2G7DCKanGvpUjXy+6S6T1j1uv 7zucP7mf1KgiaRbRG67HbCWiRmIDL0ccMfzH37rcv1y1T8EXLtHlWxIjWowDkwrkII96 Loo+LSfdedF04+E0lnWCicdShi0mFVZryCWgeFgkiMeB7V5QwTdFnJxSF2ur74NzVWzK p5Ou37xYySAdTPJsuetkBq6WG7nAPYDt6gYaI97lm98eQKt0JWI4PfQs2uAvRYk6mUWl m8qSbAS1piJHWHEFqiIrgWEd3B/aO46mubMiVSk1gEhhVYR3fGbSwEpqdUw4RXtzB1GY gI2Q== X-Gm-Message-State: AOAM530KoajqqMmpl9g25lO/7pZP2WCdR/7KQarWn+NWpl2+1QyDroEB ZGaLmo48u0RKcKXH5/OCbZEja5RMegA1LCEP X-Google-Smtp-Source: ABdhPJzw2FfKYWRpETJ2CGWF+MsVjit8cX0ifpOpG3hSiUIQDcu7mFeD8H+Ej3zhkang1BLASIi9iQ== X-Received: by 2002:a05:651c:1119:: with SMTP id d25mr406646ljo.357.1603347713663; Wed, 21 Oct 2020 23:21:53 -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 p7sm135976ljj.47.2020.10.21.23.21.52 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 21 Oct 2020 23:21:52 -0700 (PDT) Subject: Re: Query Performance / Planner estimate off To: Michael Lewis Cc: Sebastian Dressler , postgres performance list References: <61c9fd35-bdb8-3fe6-bda0-81c7e6a9eaf8@duneanalytics.com> From: Mats Olsen Message-ID: <0e7bd7e2-84c9-f261-9129-6eb4cf380b48@duneanalytics.com> Date: Thu, 22 Oct 2020 08:21:46 +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="------------0D83B4E533F857280CCAA0BA" 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. --------------0D83B4E533F857280CCAA0BA Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 10/21/20 5:29 PM, Michael Lewis wrote: > > > On Wed, Oct 21, 2020, 8:42 AM Mats Olsen > wrote: > > > On 10/21/20 2:38 PM, Sebastian Dressler wrote: >> Hi Mats, >> >>> On 20. Oct 2020, at 11:37, Mats Julian Olsen >>> > wrote: >>> >>> [...] >>> >>> 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? >> >> I looked at the plans and your config and there are some thoughts >> I'm having: >> >> - The row estimate is off, as you possibly noticed. This can be >> possibly solved by raising `default_statistics_target` to e.g. >> 2500 (we typically use that) and run ANALYZE > I've `set default_statistics_target=2500` and ran analyze on both > tables involved, unfortunately the plan is the same. The columns > we use for joining here are hashes and we expect very few > duplicates in the tables. Hence I think extended statistics > (storing most common values and histogram bounds) aren't useful > for this kind of data. Would you say the same thing? > > > Have you checked if ndistinct is roughly accurate? It can be set > manually on a column, or set to some value less than one with the > calculation depending on reltuples. Thank you for your reply! I included ndistinct-counts in the gist: see https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/24ca1f227940b48842a03435b731f82364f3576d/stats%2520Mint and https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/24ca1f227940b48842a03435b731f82364f3576d/stats%2520transactions. The join keys `transactions.hash` (unique) and `"Pair_evt_Mint".evt_tx_hash` (nearly unique) both have ndistinct=-1 which seems to make sense to me. The Mint-table has -0.8375 for evt_block_time whereas this query returns 0.56 `select count(distinct evt_block_time)::numeric/count(*) from uniswap_v2."Pair_evt_Mint";`. Should I adjust that one? Many of the other ndistinct-values for `transactions` seem strange, as it's a giant (partitioned) table, but I don't know enough about the statistics to draw any conclusions from it. What do you think? --------------0D83B4E533F857280CCAA0BA Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit


On 10/21/20 5:29 PM, Michael Lewis wrote:


On Wed, Oct 21, 2020, 8:42 AM Mats Olsen <mats@duneanalytics.com> wrote:


On 10/21/20 2:38 PM, Sebastian Dressler wrote:
Hi Mats,

On 20. Oct 2020, at 11:37, Mats Julian Olsen <mats@duneanalytics.com> wrote:

[...]

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?

I looked at the plans and your config and there are some thoughts I'm having:

- The row estimate is off, as you possibly noticed. This can be possibly solved by raising `default_statistics_target` to e.g. 2500 (we typically use that) and run ANALYZE
I've `set default_statistics_target=2500` and ran analyze on both tables involved, unfortunately the plan is the same. The columns we use for joining here are hashes and we expect very few duplicates in the tables. Hence I think extended statistics (storing most common values and histogram bounds) aren't useful for this kind of data. Would you say the same thing?

Have you checked if ndistinct is roughly accurate? It can be set manually on a column, or set to some value less than one with the calculation depending on reltuples.
Thank you for your reply!

I included ndistinct-counts in the gist: see https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/24ca1f227940b48842a03435b731f82364f3576d/stats%2520Mint and https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/24ca1f227940b48842a03435b731f82364f3576d/stats%2520transactions.

The join keys `transactions.hash` (unique) and `"Pair_evt_Mint".evt_tx_hash` (nearly unique) both have ndistinct=-1 which seems to make sense to me. The Mint-table has -0.8375 for evt_block_time whereas this query returns 0.56 `select count(distinct evt_block_time)::numeric/count(*) from uniswap_v2."Pair_evt_Mint";`. Should I adjust that one?

Many of the other ndistinct-values for `transactions` seem strange, as it's a giant (partitioned) table, but I don't know enough about the statistics to draw any conclusions from it. What do you think?

--------------0D83B4E533F857280CCAA0BA--