From: Tom Lane <tgl@sss.pgh.pa.us>
To: Giorgio Valoti <giorgio_v@mac.com>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Views and functions returning sets of records
Date: Sun, 23 Mar 2008 11:28:52 -0400
Message-ID: <8000.1206286132@sss.pgh.pa.us> (raw)
In-Reply-To: <D49B7437-F5A9-44FC-807F-C8472C059B2B@mac.com>
References: <6A737ADC-2A11-4A01-8ACE-C439E4297273@mac.com>
<29705.1206203639@sss.pgh.pa.us>
<20080322165551.GA19460@KanotixBox>
<D49B7437-F5A9-44FC-807F-C8472C059B2B@mac.com>
Giorgio Valoti <giorgio_v@mac.com> writes:
> Are there any way to pass some hints to the planner? For example,
> could the IMMUTABLE/STABLE/VOLATILE modifiers be of some help?
Those don't really do anything for set-returning functions at the
moment.
As of 8.3 there is a ROWS attribute for SRFs that can help with one
of the worst problems, namely that the planner has no idea how many
rows a SRF might return. It's simplistic (just an integer constant
estimate) but better than no control at all.
As of CVS HEAD (8.4 to be) there's a capability in the planner to
"inline" SRFs that are single SELECTs in SQL language, which should
pretty much eliminate the performance differential against a comparable
view. Unfortunately 8.4 release is at least a year away, but just
so you know. (I suppose if you were desperate enough to run a privately
modified copy, that patch should drop into 8.3 easily enough.) IIRC
the restrictions for this to happen are
* single SELECT
* function declared to return set
* function NOT declared strict or volatile
* function NOT declared SECURITY DEFINER or given any
local parameter settings
The latter restrictions are needed so that inlining doesn't change
the semantics.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, giorgio_v@mac.com, akretschmer@spamfence.net
Subject: Re: Views and functions returning sets of records
In-Reply-To: <8000.1206286132@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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