Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ib6bz-0000ZB-KF for pgsql-bugs@arkaria.postgresql.org; Sat, 30 Nov 2019 17:32:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ib6by-0007Js-1P for pgsql-bugs@arkaria.postgresql.org; Sat, 30 Nov 2019 17:32:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ib6bx-0007Jk-PW for pgsql-bugs@lists.postgresql.org; Sat, 30 Nov 2019 17:32:49 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ib6bv-0001jc-4b for pgsql-bugs@lists.postgresql.org; Sat, 30 Nov 2019 17:32:49 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xAUHWces030792; Sat, 30 Nov 2019 12:32:38 -0500 From: Tom Lane To: Michael Paquier cc: Alvaro Herrera , David Rowley , Marko Tiikkaja , PostgreSQL mailing lists Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5 In-reply-to: <20191129021833.GB2505@paquier.xyz> References: <20190905232014.GA32014@alvherre.pgsql> <20191129021833.GB2505@paquier.xyz> Comments: In-reply-to Michael Paquier message dated "Fri, 29 Nov 2019 11:18:33 +0900" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <30790.1575135158.1@sss.pgh.pa.us> Date: Sat, 30 Nov 2019 12:32:38 -0500 Message-ID: <30791.1575135158@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Michael Paquier writes: > On Thu, Sep 05, 2019 at 07:20:14PM -0400, Alvaro Herrera wrote: >> So does this proposal fix the problem that David was describing earlier? >> If it does, Tom, would you like to get this finalized and pushed? > Tom, David, we still have this patch registered as a bug fix in the CF > app. Are you planning to look at it and potentially commit something? I don't think we have any committable patch at the moment. The "stopgap" patch I posted seems unacceptable in view of the counterexample David found, while his original partial-revert patch doesn't look any better than it did then. It's possible we could salvage the "stopgap" patch with the idea David mentioned, >>> One thing we could look at would be to charge an additional >>> cpu_tuple_cost per outer row for all joins except semi, anti and >>> unique joins. This would account for the additional lookup for >>> another matching row which won't be found and cause the planner to >>> slightly favour keeping the unique rel on the inner side of the join, >>> when everything else is equal. which'd help break ties in the right direction. It's a bit scary to be fixing this issue by changing the cost estimates for non-unique joins --- that could have side-effects we don't want. But arguably, the above is a correct refinement to the cost model, so maybe it's okay. Or, since I think we're out of the realm of what would be sane to back-patch anyway, maybe it's time to take two steps back and try to find a real, non-stopgap solution. As I said upthread, I think the core problem here is failure to distinguish quals associated with the uniqueness condition from additional "filter" quals that have to be checked anyway. I wonder how hard that is to fix. regards, tom lane