Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0VsH-0003XV-TG for pgsql-bugs@arkaria.postgresql.org; Thu, 13 Sep 2018 17:57:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g0VsF-00074Q-Tu for pgsql-bugs@arkaria.postgresql.org; Thu, 13 Sep 2018 17:57:51 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0VsF-00074H-K7 for pgsql-bugs@lists.postgresql.org; Thu, 13 Sep 2018 17:57:51 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0VsD-0000xw-6b for pgsql-bugs@lists.postgresql.org; Thu, 13 Sep 2018 17:57:50 +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 w8DHvjjZ012830; Thu, 13 Sep 2018 13:57:46 -0400 From: Tom Lane To: marko@joh.to cc: pgsql-bugs@lists.postgresql.org, David Rowley Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5 In-reply-to: <153683552113.22350.18441286362867559841@wrigleys.postgresql.org> References: <153683552113.22350.18441286362867559841@wrigleys.postgresql.org> Comments: In-reply-to =?utf-8?q?PG_Bug_reporting_form?= message dated "Thu, 13 Sep 2018 10:45:21 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <12828.1536861465.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 13 Sep 2018 13:57:45 -0400 Message-ID: <12829.1536861465@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk =3D?utf-8?q?PG_Bug_reporting_form?=3D writes: > QUERY PLAN > ------------------------------------------------------------------------= ---------- > Hash Join (cost=3D303.19..315.81 rows=3D333 width=3D4) > Hash Cond: (gs2.i =3D gs1.i) > Join Filter: (expensive_func((gs1.i + gs2.i)) > 0) > -> Function Scan on generate_series gs2 (cost=3D0.00..10.00 rows=3D= 1000 > width=3D4) > -> Hash (cost=3D159.75..159.75 rows=3D11475 width=3D4) > -> Seq Scan on unique_inner gs1 (cost=3D0.00..159.75 rows=3D1= 1475 > width=3D4) > (6 rows) > (Notice how even though the function is expected to be called at least 3= 33 > times, the cost doesn't account for even a single call.) Yeah. This evidently got broken sometime during v10 development, because 9.6 and below generate a more reasonable cost: Hash Join (cost=3D270.00..25298.75 rows=3D333 width=3D4) Hash Cond: (gs2.i =3D gs1.i) Join Filter: (expensive_func((gs1.i + gs2.i)) > 0) -> Function Scan on generate_series gs2 (cost=3D0.00..10.00 rows=3D10= 00 width=3D4) -> Hash (cost=3D145.00..145.00 rows=3D10000 width=3D4) -> Seq Scan on unique_inner gs1 (cost=3D0.00..145.00 rows=3D100= 00 width=3D4) > Dropping the primary key constraint makes the costs more reasonable Interesting. That sort of points the finger in the direction of the inner_unique patch, though it could be elsewhere. Will look into it if nobody beats me to it. regards, tom lane