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 1qBvJO-0004Wp-H0 for pgsql-general@arkaria.postgresql.org; Wed, 21 Jun 2023 10: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 1qBvJN-00033m-0n for pgsql-general@arkaria.postgresql.org; Wed, 21 Jun 2023 10:43:41 +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 1qBvJM-00031W-M5 for pgsql-general@lists.postgresql.org; Wed, 21 Jun 2023 10:43:40 +0000 Received: from mail-wr1-x42c.google.com ([2a00:1450:4864:20::42c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qBvJJ-003Plx-9W for pgsql-general@lists.postgresql.org; Wed, 21 Jun 2023 10:43:38 +0000 Received: by mail-wr1-x42c.google.com with SMTP id ffacd0b85a97d-312863a983fso856672f8f.2 for ; Wed, 21 Jun 2023 03:43:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1687344215; x=1689936215; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=05LifQGpwLA90emPLelg4+dBoD4wU5uXU1/qqqIxR7Q=; b=Z0OJbuHdCUOBuLIJwjXgBgn0KwTKDdtZIRRkeDiVYrkhe8JWwu54neqP1rcIwVzgkv WRe0XWAO0yDF75EuzUUpqK2czPjCfcaIpcGCwbzgB/gwHtFpC721+MNRgmn/tS4/813k Q6/AC9r0CjLatbDsNj+18R6ho8N3C6MIpcZ1wAa+BobD0JnVUr+7FPUHO8MFeLIwDjFi I/A8LuLE+9o91k0aSiIKeB4dEdjZvn/UUrDf+IPt0P+VMYglNo2p0vJQljC3BNgh4VKR 9Et3JS71NJtXJWbKjb2I5zMGR5zOoE1k3zQ22+DoyosNNvTjPBSky77fSlBaF48oq1D1 rLsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687344215; x=1689936215; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=05LifQGpwLA90emPLelg4+dBoD4wU5uXU1/qqqIxR7Q=; b=KlWlieCASDg0PY/1g47iq9nUPmNi+Qh0VwjgbaQRw6P8XcF0mb82/I+IIP2miG+y/n W4Lpsi9j+onlTBis9bHvJTnmDPvZFizHVSiOD9GQVLgCDQdsnsR10ejtH9TtmRGBWJqW mwE+vW6w1s7EIQedeTeMnSCEHuE+l8ljOMZeB7yfqHYTsJ3AUgga7AacAfuwvKY61rQa 8QJxXtWJdFRjtVLFjmVH6VHornyB7Uw5nNTY6IEmGNuD+w3nlLZjiEWme/kdeTNeK/xy gBtRM9tQwrG/xkpYnLca73A4amj7fF/V62VK334GjhUBk3FygWgLyARi0IVdefS88RT1 tZwg== X-Gm-Message-State: AC+VfDyL+GJbseowWQUFLdTvpA3MT/1uLJ7mKYTfMQhPbS0GVahfgE/3 Glv2JCbk7Ip8YBqr+WyUmZxf/w== X-Google-Smtp-Source: ACHHUZ5k+1tTqVvba7Ua/Bj3nOTaPnAXzpIlxvEDZDyDMths0UirKtcFixMOcDlklCLxvMLnol0Bkw== X-Received: by 2002:a5d:58e5:0:b0:30a:d7a7:3db4 with SMTP id f5-20020a5d58e5000000b0030ad7a73db4mr11104062wrd.12.1687344215502; Wed, 21 Jun 2023 03:43:35 -0700 (PDT) Received: from [10.137.0.17] (static-84-42-175-93.bb.vodafone.cz. [84.42.175.93]) by smtp.gmail.com with ESMTPSA id a1-20020adff7c1000000b0030fafcbbd33sm4101480wrq.50.2023.06.21.03.43.34 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 21 Jun 2023 03:43:34 -0700 (PDT) Message-ID: Date: Wed, 21 Jun 2023 12:43:33 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.1 Subject: Re: pb with join plan Content-Language: en-US To: Marc Millas , David Rowley Cc: "pgsql-general@lists.postgresql.org" References: From: Tomas Vondra In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/21/23 00:26, Marc Millas wrote: > > > On Tue, Jun 20, 2023 at 11:19 PM David Rowley > wrote: > > On Wed, 21 Jun 2023 at 08:34, Marc Millas > wrote: > > > > On Tue, Jun 20, 2023 at 10:14 PM David Rowley > > wrote: > >> > >> On Wed, 21 Jun 2023 at 07:42, Marc Millas > wrote: > >> > But if I do the same with clause one OR clause 2, I have to  > kill the request after an hour, seeing the filesystem showing more > than 140 Mb of increased usage. > >> > >> > > link to the anonymized plan of the req with one clause : > https://explain.depesz.com/s/TWp4 > > link to the plan with the second > clause alone: https://explain.depesz.com/s/byW5 >   > link to the plan with both clauses ORed (the one not > finishing) https://explain.depesz.com/s/jHO2 > > > > > It's quite difficult to know what the problem is you want to fix here. > Your initial post indicated it was the query with the OR condition > that was causing you the problems, but the plan you've posted has no > OR condition?! > > You're more likely to get help here if you take time to properly > explain the situation and post the information that's actually > relevant to the problem you're having, or state the problem more > clearly, as there's a mismatch somewhere. > > It might also be worth having a look at > https://wiki.postgresql.org/wiki/Slow_Query_Questions > . EXPLAIN is not > going to tell us what part of the query is slow. I'll let the wiki > page guide you into what to do instead. > >   > I know that page. obviously, as I have to kill the request, I cannot > provide a explain analyze...  > It's a bit weird the "victor" table is joined seemingly without any join conditions, leading to a cross join (which massively inflates the cost for joins above it). Maybe the anonymized plan mangles it somehow. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company