Re: BUG #15383: Join Filter cost estimation problem in 10.5

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

In response to

Responses

Browse pgsql-bugs by date

  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

Browse pgsql-hackers by date

  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