From: Tom Lane <tgl@sss.pgh.pa.us>
To: marko@joh.to
Cc: pgsql-bugs@lists.postgresql.org, David Rowley <david.rowley@2ndquadrant.com>
Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5
Date: Thu, 13 Sep 2018 13:57:45 -0400
Message-ID: <12829.1536861465@sss.pgh.pa.us> (raw)
In-Reply-To: <153683552113.22350.18441286362867559841@wrigleys.postgresql.org>
References: <153683552113.22350.18441286362867559841@wrigleys.postgresql.org>
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> QUERY PLAN
> ----------------------------------------------------------------------------------
> Hash Join (cost=303.19..315.81 rows=333 width=4)
> Hash Cond: (gs2.i = gs1.i)
> Join Filter: (expensive_func((gs1.i + gs2.i)) > 0)
> -> Function Scan on generate_series gs2 (cost=0.00..10.00 rows=1000
> width=4)
> -> Hash (cost=159.75..159.75 rows=11475 width=4)
> -> Seq Scan on unique_inner gs1 (cost=0.00..159.75 rows=11475
> width=4)
> (6 rows)
> (Notice how even though the function is expected to be called at least 333
> 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=270.00..25298.75 rows=333 width=4)
Hash Cond: (gs2.i = gs1.i)
Join Filter: (expensive_func((gs1.i + gs2.i)) > 0)
-> Function Scan on generate_series gs2 (cost=0.00..10.00 rows=1000 width=4)
-> Hash (cost=145.00..145.00 rows=10000 width=4)
-> Seq Scan on unique_inner gs1 (cost=0.00..145.00 rows=10000 width=4)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, marko@joh.to, david.rowley@2ndquadrant.com
Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5
In-Reply-To: <12829.1536861465@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox