To: Jay Levitt <jay.levitt@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Predicates not getting pushed into SQL function? 
In-reply-to: <4EB2D3CB.7020708@gmail.com> 
References: <4EB2D3CB.7020708@gmail.com>
Comments: In-reply-to Jay Levitt <jay.levitt@gmail.com>
	message dated "Thu, 03 Nov 2011 13:47:55 -0400"
Date: Thu, 03 Nov 2011 14:41:16 -0400
Message-ID: <6646.1320345676@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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