pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedView containing a recursive function
3+ messages / 2 participants
[nested] [flat]
* View containing a recursive function
@ 2016-02-01 07:23 Mathieu De Zutter <mathieu@dezutter.org>
0 siblings, 1 reply; 3+ messages in thread
From: Mathieu De Zutter @ 2016-02-01 07:23 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have a recursive part in my database logic that I want to isolate and
reuse as a view. I had found a blog that explained how move a function
parameter into a view. The SQL is in attachment.
When I write a query based on that view with a fixed value (or values) for
the (input) parameter, the planner does fine and only evaluates the
function once.
However, when the value of the parameter should be deduced from something
else, the planner doesn't understand that and will evaluate the function
for each possible value.
Any pointers to what I'm doing wrong or on how to optimize it?
Attachment contains the queries and explain plans.
Thanks!
Kind regards,
Mathieu
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
recursive_view.txt (text/plain, 3.1K) download
CREATE OR REPLACE FUNCTION fn_covering_works(wid INTEGER)
RETURNS TABLE(work_id INTEGER)
AS
$$
WITH RECURSIVE func(work_id) AS
(
SELECT wid
UNION ALL
SELECT ad.adapted_id
FROM func f JOIN adaptation ad ON f.work_id = ad.original_id
)
SELECT work_id
FROM func
$$
LANGUAGE 'sql' ROWS 1 COST 10000;
CREATE OR REPLACE VIEW covering_works_r AS
SELECT
w.id AS work_id,
fn_covering_works(w.id) AS covering_work_id
FROM work w;
-- This one is fine
EXPLAIN ANALYZE
SELECT
w.id,
cw.covering_work_id
FROM work w
JOIN covering_works_r cw ON cw.work_id = w.id
WHERE w.id = 4249;
id | covering_work_id
------+------------------
4249 | 4249
4249 | 102813
4249 | 4250
4249 | 23551
4249 | 68931
4249 | 74836
4249 | 76088
4249 | 111423
4249 | 112399
4249 | 112502
4249 | 112666
4249 | 120640
4249 | 126994
4249 | 133918
4249 | 139519
4249 | 142989
4249 | 149393
4249 | 111424
"Nested Loop (cost=0.58..33.64 rows=1 width=8) (actual time=0.334..0.424 rows=18 loops=1)"
" -> Index Only Scan using work_pkey on work w (cost=0.29..4.31 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)"
" Index Cond: (id = 4249)"
" Heap Fetches: 0"
" -> Index Only Scan using work_pkey on work w_1 (cost=0.29..29.31 rows=1 width=4) (actual time=0.309..0.393 rows=18 loops=1)"
" Index Cond: (id = 4249)"
" Heap Fetches: 0"
"Total runtime: 0.457 ms"
-- This one is too slow, but should be as fast as the first query.
-- At first sight it seems right, but the condition w_1.id=4249 (=w.id) isn't pushed to the second index scan.
EXPLAIN ANALYZE
SELECT
w.id,
cw.covering_work_id
FROM work w
JOIN covering_works_r cw ON cw.work_id = w.id
WHERE w.first_release_id = 4249;
id | covering_work_id
------+------------------
4249 | 4249
4249 | 102813
4249 | 4250
4249 | 23551
4249 | 68931
4249 | 74836
4249 | 76088
4249 | 111423
4249 | 112399
4249 | 112502
4249 | 112666
4249 | 120640
4249 | 126994
4249 | 133918
4249 | 139519
4249 | 142989
4249 | 149393
4249 | 111424
"Nested Loop (cost=0.58..1659529.05 rows=1 width=8) (actual time=30.075..995.889 rows=18 loops=1)"
" Join Filter: (w.id = w_1.id)"
" Rows Removed by Join Filter: 81228"
" -> Index Scan using work_first_release_idx on work w (cost=0.29..8.31 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)"
" Index Cond: (first_release_id = 4249)"
" -> Index Only Scan using work_pkey on work w_1 (cost=0.29..1658030.07 rows=66252 width=4) (actual time=0.185..981.054 rows=81246 loops=1)"
" Heap Fetches: 0"
"Total runtime: 995.916 ms"
# select id, first_release_id from work w where id = 4249;
id | first_release_id
------+------------------
4249 | 4249
* Re: View containing a recursive function
@ 2016-02-01 09:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mathieu De Zutter <mathieu@dezutter.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2016-02-01 09:44 UTC (permalink / raw)
To: Mathieu De Zutter <mathieu@dezutter.org>; +Cc: pgsql-performance
Mathieu De Zutter <mathieu@dezutter.org> writes:
> I have a recursive part in my database logic that I want to isolate and
> reuse as a view. I had found a blog that explained how move a function
> parameter into a view. The SQL is in attachment.
> When I write a query based on that view with a fixed value (or values) for
> the (input) parameter, the planner does fine and only evaluates the
> function once.
> However, when the value of the parameter should be deduced from something
> else, the planner doesn't understand that and will evaluate the function
> for each possible value.
I do not think this has anything to do with whether the query inside the
function is recursive. Rather, the problem is that the view has a
set-returning function in its targetlist, which prevents the view from
being flattened into the outer query, per this bit in is_simple_subquery():
/*
* Don't pull up a subquery that has any set-returning functions in its
* targetlist. Otherwise we might well wind up inserting set-returning
* functions into places where they mustn't go, such as quals of higher
* queries. This also ensures deletion of an empty jointree is valid.
*/
if (expression_returns_set((Node *) subquery->targetList))
return false;
Lack of flattening disables a lot of join optimizations, including the
one you want.
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.
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.
Doing both of those things gives me a plan like this:
Nested Loop (cost=448.24..509.53 rows=1131 width=4)
-> Nested Loop (cost=0.31..16.36 rows=1 width=8)
-> Index Scan using work_first_release_id_idx on work w (cost=0.15..8.17 rows=1 width=4)
Index Cond: (first_release_id = 4249)
-> Index Only Scan using work_pkey on work w_1 (cost=0.15..8.17 rows=1 width=4)
Index Cond: (id = w.id)
-> CTE Scan on func (cost=447.93..470.55 rows=1131 width=0)
CTE func
-> Recursive Union (cost=0.00..447.93 rows=1131 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Hash Join (cost=0.33..42.53 rows=113 width=4)
Hash Cond: (ad.original_id = f.work_id)
-> Seq Scan on adaptation ad (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=0.20..0.20 rows=10 width=4)
-> WorkTable Scan on func f (cost=0.00..0.20 rows=10 width=4)
which looks hairier, but that's because the function has been inlined
which is usually what you want for a SQL-language function. The join
is happening the way you want.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: View containing a recursive function
@ 2016-02-05 15:43 Mathieu De Zutter <mathieu@dezutter.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Mathieu De Zutter @ 2016-02-05 15:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Mon, 1 Feb 2016 at 10:45 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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)
-> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-02-05 15:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-01 07:23 View containing a recursive function Mathieu De Zutter <mathieu@dezutter.org>
2016-02-01 09:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-02-05 15:43 ` Mathieu De Zutter <mathieu@dezutter.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox