Mathieu De Zutter <mathieu@dezutter.org> writes: Assuming you have a reasonably late-model PG, you could rewrite the view with a lateral function call:
CREATE OR REPLACE VIEW covering_works_r AS SELECT w.id AS work_id, fn.f AS covering_work_id FROM work w, fn_covering_works(w.id) as fn(f);
which puts the SRF into FROM where the planner can deal with it much better.
Thanks a lot. That fixes it!
Another problem is that you let the function default to being VOLATILE, which would have disabled view flattening even if this didn't. I see no reason for this function not to be marked STABLE.
By marking it STABLE, it ignores my row estimate of 1 - I guess because of the inlining. The number of results is usually just 1, though the number can go up to 10 in exceptional cases. That's still a lot better than the inexplicable estimate of the planner (101) when marked STABLE, which often leads to triggering a hash join instead of a nested loop in complex queries:
-> Recursive Union (cost=0.00..795.53 rows=101 width=4) (actual time=0.001..0.009 rows=1 loops=4)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=4)