Re: Predicates not getting pushed into SQL function? - Mailing list pgsql-performance

From Tom Lane
Subject Re: Predicates not getting pushed into SQL function?
Date
Msg-id 6646.1320345676@sss.pgh.pa.us
Whole thread Raw
In response to Predicates not getting pushed into SQL function?  (Jay Levitt <jay.levitt@gmail.com>)
Responses Re: Predicates not getting pushed into SQL function?  (Jay Levitt <jay.levitt@gmail.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Optimization required for multiple insertions in PostgreSQL
Next
From: Tom Lane
Date:
Subject: Re: Blocking excessively in FOR UPDATE