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 1pY1p4-0000Ga-HF for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 09:35:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pY1p3-00025l-3h for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 09:35:29 +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 1pY1p2-00025c-PA for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 09:35:28 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pY1oy-0001ZM-0H for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 09:35:28 +0000 Received: by mail-ed1-x535.google.com with SMTP id x3so7703699edb.10 for ; Fri, 03 Mar 2023 01:35:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:cc:to:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=1Us9N0F0yde5ENioWlUc7gIhwC7NrSdy9w/PhP8VbmI=; b=XLgdZDaTbky9OSV5TF1cpP2mrqgf2h+GDekXb4OUcOSxw3s4KthRGkLGWLpEZSK+xA p/DsQLh4alTOwTH9NHc+hv0SW5N+4eSf4cVPO73m7RfY2W5nbE6sB/Trs65af8WgVXh6 zK++P+5Yi89XaQYI6XC86W1rUTssnJaaH0UJnesU5Kcu9K3qpOdZHt4Yj/HZP3IszMxw yQcfBm3f0e0uP9qke1am/V6Mjme6Ap0rhquaG8jsTLzoCDHbPcD3ukrheAooPwCJEqZK euW936zZyYUR/T5+n3joBXJ3JPjzRj8/6Irn9IqYFvijSAr5YIVZquejLP3jg1d4wCxI oinA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:cc:to:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=1Us9N0F0yde5ENioWlUc7gIhwC7NrSdy9w/PhP8VbmI=; b=xJgHLMs7JDUOYReTg3Dp/YluxB60PCGL6Gr4NDc8Na5RRVau8goShy8coK0hYVU3Mj 7Wvemfa5NUKTOi6fwlmjn/xF9YDcB4zchXKKr3uatpykp5CvLJKYmt/eU2tUP5Mltus7 77V+VBBKBkEb9dhoGoMZBHSiTviARe6WKL5W0joNQqrmLOYM0tP9tL28m/nY37aDVhm4 CeTZ7hO7V2p8L03Kt62c+940XhZNKBm46bpRd72hfm9u4Z82XfGtd7o5A/i4CKUIfLql O/temZ2/ZO/APhAl5LSo7t4LJF+IXyp0ZurMcLAxiMg7Aysbo1s4kqjFBBtpFVs3WZnG BYpg== X-Gm-Message-State: AO0yUKVPw8fU90SkWnNWnRnTUuuO5bg9u3nXmpST2wPSdyNGh/0w5rJy 7rIV/OAI5kQmuMKi5Iu9df4= X-Google-Smtp-Source: AK7set+AgRMXG2AjsfUuWAk+817K7yTfrU8rQY6cFskcxS42NVlAiF/GOcq4fLSsoSjGctQ3zi/v/w== X-Received: by 2002:a05:6402:1619:b0:4bc:edde:150d with SMTP id f25-20020a056402161900b004bcedde150dmr4870479edv.0.1677836122548; Fri, 03 Mar 2023 01:35:22 -0800 (PST) Received: from [192.168.1.6] (BSN-95-228-180.static.siol.net. [193.95.228.180]) by smtp.gmail.com with ESMTPSA id f21-20020a50d555000000b004c059535b43sm952442edj.31.2023.03.03.01.35.21 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 03 Mar 2023 01:35:22 -0800 (PST) Message-ID: <01101f47-e596-cc72-cd7c-d9d027c9b9ef@gmail.com> Date: Fri, 3 Mar 2023 10:35:21 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: Postgres undeterministically uses a bad plan, how to convince it otherwise? To: David Rowley Cc: pgsql-general@lists.postgresql.org References: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> <9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com> <0701777f-d9ab-e406-9b90-539eac169410@gmail.com> From: cen In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > Likely to be safe, you'd just include both. The problem is that the > query planner makes use of equivalence classes to deduce equivalence > in quals. > > If you have a query such as: > > select * from t1 inner join t2 on t1.x = t2.y where t1.x = 3; > > then the planner can deduce that t2.y must also be 3 and that qual can > be pushed down to the scan level. If t2.y = 3 is quite selective and > there's an index on that column, then this deduction is likely going > to be a very good win, as the alternative of not using it requires > looking at all rows in t2. Does equivalency only work for constants as in the sample you provided or will it also be found in b1."number" and t0."block_number" in my sample query? Meaning the columns could be used interchangeably in all the WHERE clauses and the ORDER clause, then it is a matter of figuring out what costs less.