From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jay Levitt <jay.levitt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Predicates not getting pushed into SQL function?
Date: Thu, 03 Nov 2011 14:41:16 -0400
Message-ID: <6646.1320345676@sss.pgh.pa.us> (raw)
In-Reply-To: <4EB2D3CB.7020708@gmail.com>
References: <4EB2D3CB.7020708@gmail.com>
Jay Levitt <jay.levitt@gmail.com> writes:
> I'm confused. I have a now-trivial SQL function that, unrestricted, would
> scan my whole users table. When I paste the body of the function as a
> subquery and restrict it to one row, it only produces one row. When I paste
> the body of the function into a view and restrict it to one row, it produces
> one row. But when I put it in a SQL function... it scans the whole users
> table and then throws the other rows away.
> I thought SQL functions were generally inline-able, push-down-able, etc.
inline-able, yes, but if they're not inlined you don't get any such
thing as pushdown of external conditions into the function body.
A non-inlined function is a black box.
The interesting question here is why the function doesn't get inlined
into the calling query. You got the obvious showstoppers: it has a
SETOF result, it's not volatile, nor strict. The only other possibility
I can see offhand is that there's some sort of result datatype mismatch,
but you've not provided enough info to be sure about that.
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, jay.levitt@gmail.com
Subject: Re: Predicates not getting pushed into SQL function?
In-Reply-To: <6646.1320345676@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