On Mon, 1 Feb 2016 at 10:45 Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Mathieu De Zutter <mathieu@xxxxxxxxxxxx> 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)
-> Nested Loop (cost=0.29..79.35 rows=10 width=4) (actual time=0.005..0.005 rows=0 loops=5)
-> WorkTable Scan on func f (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=5)
-> Index Scan using adaptation_adapted_idx on adaptation ad (cost=0.29..7.91 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=5)
Index Cond: (adapted_id = f.work_id)
Thanks again,
Mathieu
