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 1kUvdY-00043A-Me for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 17:41:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kUvd1-0005KF-OO for pgsql-performance@arkaria.postgresql.org; Tue, 20 Oct 2020 17:40:55 +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 1kUvd1-0005K1-Gk for pgsql-performance@lists.postgresql.org; Tue, 20 Oct 2020 17:40:55 +0000 Received: from mail-lj1-x235.google.com ([2a00:1450:4864:20::235]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kUvcu-0003hw-DN for pgsql-performance@postgresql.org; Tue, 20 Oct 2020 17:40:54 +0000 Received: by mail-lj1-x235.google.com with SMTP id d24so2924433ljg.10 for ; Tue, 20 Oct 2020 10:40:47 -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=mfrkKQm1KFU4/Bp6OW3WIfuybr9HCj246lM6CJzdgPM=; b=XxGFp/J99H4Ej/8L1L95GoZsHRxhiu03IYE4tRews9i6Ivs6AGMPWqkMZ9miKfBd/E m75dfC1LwPT3vnjS0T5gx2sKYvqot8Mkip3f6l5vaKeg/K3Dp7hoO26Vax8Yd+kUBg6l 9rL1zgrfrfzDRufbzIl7K6IFhUhldLQip0IfzseOXn4+iTHcDC2fp6d4lRpgYHz4TgsQ GymIk1PnmvzZB9HlSsXkqEj8+u3/1lcngj4c73FmNEHbZyiSMeyhRegoTt3cEkhe6PvM vC4Wr0eP6OtpwSZI7mVuXOiRwVvAQB4Cc6eEzi8E1Ms/YGNYzRVJ80TkrAzNlq4FiqHb QPiA== 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=mfrkKQm1KFU4/Bp6OW3WIfuybr9HCj246lM6CJzdgPM=; b=q3ZZop+RKcFkzMmCnlE4rKQMI9kBxKEpaOcbWv+ML21Og6WsXaO2U8U+UxoD0KK2N/ 6yTScpS5n8pYTLHac+2utyyt7DydvtPoPrk0VNYmYuiZqjdDqe/pfegOcFQDHyuJD/lH 0YebU8Vq02Oqz1xHc17zOuZnLCj/MoQF1KInRm9MECeqD533s63t1io3a5yVP9AHYh/G tgoaw5DtBz8Hcr4hlgOqr05Ym0pEVqZp3b2WBWscH5/S1jvPJWY5cJ9EkjngDwOpBc4K AaEowiaaEhBq0++0l+D/1LMUtm+3882Dp0oGrGgiNOCukxh1Uf7FUBZCJ9PpvTdEVCwF pMMw== X-Gm-Message-State: AOAM531qS99ApKs4Dgqxc6HvnFEbGQWF9uANmEAhqc8EJKpOOP9zmzml zV1Mvn6h6nQyCtBzxHV1jKPp10CIROUU5KGJ X-Google-Smtp-Source: ABdhPJyB8atf9LutL6uEC8/mwNuuNmccikFK5Xb9QRcqsJ82ZWXbiXXtq8bJ3BlTuveS5fR+kFr8ZQ== X-Received: by 2002:a2e:9117:: with SMTP id m23mr1558659ljg.287.1603215646839; Tue, 20 Oct 2020 10:40:46 -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 f21sm399495lfc.122.2020.10.20.10.40.45 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 20 Oct 2020 10:40:45 -0700 (PDT) Subject: Re: Query Performance / Planner estimate off To: Victor Yegorov Cc: pgsql-performance@postgresql.org References: From: Mats Olsen Message-ID: <1a470915-3ddd-ddc8-c1dd-45cf648fe392@duneanalytics.com> Date: Tue, 20 Oct 2020 19:40:40 +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="------------8031E05101DAE8165DA04226" 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. --------------8031E05101DAE8165DA04226 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 10/20/20 3:22 PM, Victor Yegorov wrote: > вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen >: > > I'm looking for some help to manage queries against two large tables. > > > Also, can you enable `track_io_timing` (no restart required) and > provide output of `EXPLAIN (analyze, buffers, settings)` for all 3 > variants, please? > (I assume you're on 12+.) Thanks! Yes on 12.2. Here's the output: 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 Are these helpful? > > -- > Victor Yegorov --------------8031E05101DAE8165DA04226 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit


On 10/20/20 3:22 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.

Also, can you enable `track_io_timing` (no restart required) and provide output of `EXPLAIN (analyze, buffers, settings)` for all 3 variants, please? 
(I assume you're on 12+.)

Thanks! Yes on 12.2. Here's the output:

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

Are these helpful?


--
Victor Yegorov
--------------8031E05101DAE8165DA04226--