Re: View containing a recursive function

On Mon, 1 Feb 2016 at 10:45 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mathieu De Zutter <mathieu(at)dezutter(dot)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)
-> 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