View containing a recursive function

From: Mathieu De Zutter <mathieu(at)dezutter(dot)org>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: View containing a recursive function
Date: 2016-02-01 07:23:40
Message-ID: CAH7GKCw50gRf75vWR75eB80kkJpO4t+o65xJ7LFgH2Fbu-Vztg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
recursive_view.txt text/plain 3.1 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Albe Laurenz 2016-02-01 09:38:31 Re: Hash join gets slower as work_mem increases?
Previous Message Hedayat Vatankhah 2016-01-30 21:50:53 Re: PostgreSQL seems to create inefficient plans in simple conditional joins