From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | marko(at)joh(dot)to |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Subject: | Re: BUG #15383: Join Filter cost estimation problem in 10.5 |
Date: | 2018-09-13 17:57:45 |
Message-ID: | 12829.1536861465@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Андрей Ковальчук | 2018-09-14 00:05:59 | Re: BUG #15382: Error create dictionary in pg_dump |
Previous Message | Tom Lane | 2018-09-13 17:42:10 | Re: PostgreSQL 10.0 SELECT LIMIT performance problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2018-09-13 18:19:47 | Re: [HACKERS] Bug in to_timestamp(). |
Previous Message | Andrew Gierth | 2018-09-13 17:50:13 | Re: Avoid extra Sort nodes between WindowAggs when sorting can be reused |