=?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