Hi,
The queries in what follows can be executed on the following fiddle: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=64542f2d987d3ce0d85bbc40ddadf7d6 - Please note that the queries/functions might look silly/pointless, I extracted the performance issue I am seeing to a minimal reproducible example.
I have the following schema:
create table parent
(
id int primary key
);
create table child
(
id int primary key,
parent_id int references parent(id)
);
create index on child(parent_id);
Let's start with the following inlinable setof-returning function which basically returns the child rows for a given parent identifier:
create function child_get_1(int) returns table(a int) as
$$
select child.id
from child
left join parent
on parent.id = child.parent_id
where child.parent_id = $1;
$$
language sql stable;
Note that the left join branch is intentionally not used, and thus could be eliminated by the planner.
When executing the following query, I get a satisfying hash (left) join (and the left join to parent is indeed eliminated):
explain analyze
select ch.* from parent p, child_get_1(p.id) ch;
+--------------------------------------------------------------------------------------------------------------------+
| Hash Join (cost=3.25..17163.23 rows=999900 width=4) (actual time=0.025..194.279 rows=999900 loops=1) |
| Hash Cond: (child.parent_id = p.id) |
| -> Seq Scan on child (cost=0.00..14424.00 rows=999900 width=8) (actual time=0.005..47.215 rows=999900 loops=1) |
| -> Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.016..0.016 rows=100 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 12kB |
| -> Seq Scan on parent p (cost=0.00..2.00 rows=100 width=4) (actual time=0.001..0.007 rows=100 loops=1) |
+--------------------------------------------------------------------------------------------------------------------+
Now, I introduce a convenience function, also inlinable, which fetches the child rows by its parent id:
create function t(int) returns setof child as
$$
select child.* from child where child.parent_id = $1;
$$
language sql stable;
I refactor `child_get_1(int)` from above as following:
create function child_get_2(int) returns table(a int) as
$$
select child.id
from t($1) child
left join parent
on parent.id = child.parent_id;
$$
language sql stable;
explain analyze
select ch.* from parent p, child_get_2(p.id) ch;
Now, I get a nested loop, which as expected performs quite badly:
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Nested Loop (cost=189.92..493990.48 rows=999900 width=4) (actual time=1.519..713.680 rows=999900 loops=1) |
| -> Seq Scan on parent p (cost=0.00..2.00 rows=100 width=4) (actual time=0.004..0.081 rows=100 loops=1) |
| -> Bitmap Heap Scan on child (cost=189.92..4739.90 rows=9999 width=4) (actual time=1.365..6.332 rows=9999 loops=100) |
| Recheck Cond: (parent_id = p.id) |
| Heap Blocks: exact=442476 |
| -> Bitmap Index Scan on child_parent_id_idx (cost=0.00..187.42 rows=9999 width=0) (actual time=0.838..0.838 rows=9999 loops=100) |
| Index Cond: (parent_id = p.id) |
+--------------------------------------------------------------------------------------------------------------------------------------------+
For some reason I cannot explain we now end up with a nested loop, instead an hash join. The fairly trivial introduction of `t(int)` messes up with reordering, but I fail to see why. I manually upped the from and join collapse limit to 32 - just to be sure -, but no effect. Also, the left join branch could not be eliminated. I believe this is related to the usage of the implicit lateral join to `child_get_2(p.id)` in the main query, which somehow messes up with the reordering of `from t($1) as child` in `child_get_2(int)`, though I am not 100% sure.
Also, note that when we apply an inner join instead of a left join, the problem goes away. The planner now manages to end up with a hash join in both cases.
I am seeing this on v10 and v11.
Any ideas?
Thank you. Best regards.
