Thread: SQL functions not being inlined

Some of my functions are running much slower than doing the same query
"inline" and I'd like to know if there's a way to fix that.

I have a number of tables that store data valid at different times. For
each logical entity there may be multiple rows, valid at different times
(sometimes overlapping). Each such table has valid_time_begin and
valid_time_end columns that specify when that data is valid, eg.

CREATE TABLE thing_timeslice
(
   timeslice_id serial NOT NULL,
   thing_id integer NOT NULL,
   valid_time_begin timestamp NOT NULL,
   valid_time_end timestamp NOT NULL,
   ... other columns ...
   CONSTRAINT pk_thing_timeslice PRIMARY KEY (timeslice_id),
   CONSTRAINT fk_thing_timeslice_thing FOREIGN KEY (thing_id) REFERENCES
thing (thing_id)
)
CREATE INDEX ix_thing_timeslice_thing_id ON thing_timeslice (thing_id);

To simplify querying for data valid at a given time I've created
functions like this:

CREATE OR REPLACE FUNCTION thing_asof(effective_time timestamp)
RETURNS SETOF thing_timeslice
AS $BODY$
     SELECT *
     FROM thing_timeslice
     WHERE ($1, '0'::interval) OVERLAPS (valid_time_begin,
COALESCE(valid_time_end, 'infinity'::timestamp))
$BODY$ LANGUAGE SQL STABLE;

I then select from these functions as though they were tables, often
joining them, eg.

SELECT *
FROM thing_asof('2012-05-01') a
JOIN another_thing_asof('2012-05-01') b ON a.thing_id =
b.referenced_thing_id

This is quite slow, especially when I have a WHERE clause that narrows
down the set of rows from 100,000 to 10 or so. I suspect it's evaluating
the function first and filtering it afterwards and perhaps not using the
index, either. If I manually "inline" my functions the queries get 10x
faster! Eg.

SELECT *
FROM
(
     SELECT *
     FROM thing_timeslice
     WHERE ('2012-05-01', '0'::interval) OVERLAPS (valid_time_begin,
COALESCE(valid_time_end, 'infinity'::timestamp))
) a
JOIN ...

My question is: why is PostgreSQL not doing this inlining automatically?
Are there some gotchas I need to be aware of or is it just not
supported? I'm running 9.1.3.

Regards,

Evan

On Wed, May 2, 2012 at 12:43 PM, Evan Martin
<postgresql@realityexists.net> wrote:
> Some of my functions are running much slower than doing the same query
> "inline" and I'd like to know if there's a way to fix that.
> [chomp analysis and examples]

Is there any possibility that you could recode your functions as
views? The optimizer can push predicates down into views but not, it
would appear, into functions.

In the specific example given, I would be looking, not for an
OVERLAPS, but a simple comparison:

SELECT * FROM thing_timeslice
WHERE '2012-05-01' BETWEEN valid_time_begin AND
COALESCE(valid_time_end, 'infinity'::timestamp)

That may make it easier for the optimizer.

ChrisA

Evan Martin <postgresql@realityexists.net> writes:
> Some of my functions are running much slower than doing the same query
> "inline" and I'd like to know if there's a way to fix that. ...
> This is quite slow, especially when I have a WHERE clause that narrows
> down the set of rows from 100,000 to 10 or so. I suspect it's evaluating
> the function first and filtering it afterwards and perhaps not using the
> index, either.

Well, rather than suspecting, you should use EXPLAIN to find out whether
the functions are inlined or not.  The particular example you give here
seems inlinable to me, but maybe some of your other cases aren't.

I concur with the other respondent that OVERLAPS is not the most
efficient way to deal with the sort of condition you have here, either.
Postgres basically doesn't know how to optimize OVERLAPS at all, whereas
the planner has heard of BETWEEN and other simple-comparison constructs.

            regards, tom lane

Thanks, Tom (and Chris). Yes, the EXPLAIN output showed a function scan:

SELECT * FROM thing_asof('2012-04-01') WHERE timeslice_id = 1234

Function Scan on thing_asof  (cost=0.25..12.75 rows=5 width=353)
   Filter: ((timeslice_id)::integer = 12345)

I replaced the OVERLAPS with < and <= comparisons (since I want the end
time to be exclusive), so the functions now look like this:

CREATE OR REPLACE FUNCTION thing_asof(effective_time timestamp)
RETURNS SETOF thing_timeslice
AS $BODY$
     SELECT *
     FROM thing_timeslice
     WHERE valid_time_begin <= $1 AND (valid_time_end IS NULL OR $1 <
valid_time_end)
$BODY$ LANGUAGE SQL STABLE;

This worked... at first. I did some simple queries and they showed the
function being inlined (index scan on primary key, seq scan - no
function scan). Very happy with that, I tried changing some other
functions (that depend on these) and then found that the _asof functions
are not being inlined anymore! I swear, I'm not making this up. Nothing
changed in those functions. Same simple query. It was inlined before and
now it's not. I've dropped and re-created the functions, did an ANALYZE,
even restarted PostgreSQL - they're not inlined any more. I really don't
know what to think!

Regards,

Evan


On 2/05/2012 1:44 PM, Tom Lane wrote:
> Evan Martin<postgresql@realityexists.net>  writes:
>> Some of my functions are running much slower than doing the same query
>> "inline" and I'd like to know if there's a way to fix that. ...
>> This is quite slow, especially when I have a WHERE clause that narrows
>> down the set of rows from 100,000 to 10 or so. I suspect it's evaluating
>> the function first and filtering it afterwards and perhaps not using the
>> index, either.
> Well, rather than suspecting, you should use EXPLAIN to find out whether
> the functions are inlined or not.  The particular example you give here
> seems inlinable to me, but maybe some of your other cases aren't.
>
> I concur with the other respondent that OVERLAPS is not the most
> efficient way to deal with the sort of condition you have here, either.
> Postgres basically doesn't know how to optimize OVERLAPS at all, whereas
> the planner has heard of BETWEEN and other simple-comparison constructs.
>
>             regards, tom lane
>

OK, it's now changed back again! I suspended the virtual machine in
which PostgreSQL runs, later resumed it, did some unrelated queries
(SELECT only) and then ran the exact same query as before. It now
returns in 15 ms and uses the index. Here's the query plan:

Index Scan using pk_thing_timeslice on thing_timeslice  (cost=0.00..8.32
rows=1 width=382) (actual time=0.058..0.060 rows=1 loops=1)
   Index Cond: ((timeslice_id)::integer = 12345)
   Filter: (((valid_time_begin)::timestamp without time zone <=
'2012-04-01 00:00:00'::timestamp without time zone) AND ((valid_time_end
IS NULL) OR ('2012-04-01 00:00:00'::timestamp without time zone <
(valid_time_end)::timestamp without time zone)))

Any idea what's going on? It seems like the query optimizer randomly
changes its mind about inlining it.

Regards,

Evan

On 2/05/2012 3:34 PM, Evan Martin wrote:
> Thanks, Tom (and Chris). Yes, the EXPLAIN output showed a function scan:
>
> SELECT * FROM thing_asof('2012-04-01') WHERE timeslice_id = 1234
>
> Function Scan on thing_asof  (cost=0.25..12.75 rows=5 width=353)
>   Filter: ((timeslice_id)::integer = 12345)
>
> I replaced the OVERLAPS with < and <= comparisons (since I want the
> end time to be exclusive), so the functions now look like this:
>
> CREATE OR REPLACE FUNCTION thing_asof(effective_time timestamp)
> RETURNS SETOF thing_timeslice
> AS $BODY$
>     SELECT *
>     FROM thing_timeslice
>     WHERE valid_time_begin <= $1 AND (valid_time_end IS NULL OR $1 <
> valid_time_end)
> $BODY$ LANGUAGE SQL STABLE;
>
> This worked... at first. I did some simple queries and they showed the
> function being inlined (index scan on primary key, seq scan - no
> function scan). Very happy with that, I tried changing some other
> functions (that depend on these) and then found that the _asof
> functions are not being inlined anymore! I swear, I'm not making this
> up. Nothing changed in those functions. Same simple query. It was
> inlined before and now it's not. I've dropped and re-created the
> functions, did an ANALYZE, even restarted PostgreSQL - they're not
> inlined any more. I really don't know what to think!
>
> Regards,
>
> Evan
>
>
> On 2/05/2012 1:44 PM, Tom Lane wrote:
>> Evan Martin<postgresql@realityexists.net>  writes:
>>> Some of my functions are running much slower than doing the same query
>>> "inline" and I'd like to know if there's a way to fix that. ...
>>> This is quite slow, especially when I have a WHERE clause that narrows
>>> down the set of rows from 100,000 to 10 or so. I suspect it's
>>> evaluating
>>> the function first and filtering it afterwards and perhaps not using
>>> the
>>> index, either.
>> Well, rather than suspecting, you should use EXPLAIN to find out whether
>> the functions are inlined or not.  The particular example you give here
>> seems inlinable to me, but maybe some of your other cases aren't.
>>
>> I concur with the other respondent that OVERLAPS is not the most
>> efficient way to deal with the sort of condition you have here, either.
>> Postgres basically doesn't know how to optimize OVERLAPS at all, whereas
>> the planner has heard of BETWEEN and other simple-comparison constructs.
>>
>>             regards, tom lane
>>
>

Evan Martin <postgresql@realityexists.net> writes:
> This worked... at first. I did some simple queries and they showed the
> function being inlined (index scan on primary key, seq scan - no
> function scan). Very happy with that, I tried changing some other
> functions (that depend on these) and then found that the _asof functions
> are not being inlined anymore! I swear, I'm not making this up. Nothing
> changed in those functions. Same simple query. It was inlined before and
> now it's not. I've dropped and re-created the functions, did an ANALYZE,
> even restarted PostgreSQL - they're not inlined any more. I really don't
> know what to think!

[ squint... ]  There are a lot of undocumented restrictions on inlining
in inline_set_returning_function(), but AFAICS none of them are
nondeterministic, nor would the decision depend on anything outside
the function and its arguments.  Can you provide a concrete test case?

            regards, tom lane

Thanks, I went into that function, added log statements everywhere and
figured which check it's failing on:

!heap_attisnull(func_tuple, Anum_pg_proc_proconfig)

and it's because my real function had this at the end:

SET search_path FROM CURRENT;

which I never imagined would make any difference. This still doesn't
explain why it was being inlined sometimes - I didn't add and remove
that bit, it was there the whole time! But at least the fix is simple -
remove it.

Is there any reason this stuff isn't documented? It can have huge
performance implications, so I'm surprised more people don't run into
it. Even better would be some query that checks whether a function is
inlineable - maybe not perfectly, but it could detect a few of the
reasons just from pg_proc, right?

Regards,

Evan

On 2/05/2012 11:41 PM, Tom Lane wrote:
> Evan Martin<postgresql@realityexists.net>  writes:
>> This worked... at first. I did some simple queries and they showed the
>> function being inlined (index scan on primary key, seq scan - no
>> function scan). Very happy with that, I tried changing some other
>> functions (that depend on these) and then found that the _asof functions
>> are not being inlined anymore! I swear, I'm not making this up. Nothing
>> changed in those functions. Same simple query. It was inlined before and
>> now it's not. I've dropped and re-created the functions, did an ANALYZE,
>> even restarted PostgreSQL - they're not inlined any more. I really don't
>> know what to think!
> [ squint... ]  There are a lot of undocumented restrictions on inlining
> in inline_set_returning_function(), but AFAICS none of them are
> nondeterministic, nor would the decision depend on anything outside
> the function and its arguments.  Can you provide a concrete test case?
>
>             regards, tom lane
>

Evan Martin <postgresql@realityexists.net> writes:
> Thanks, I went into that function, added log statements everywhere and
> figured which check it's failing on:
> !heap_attisnull(func_tuple, Anum_pg_proc_proconfig)
> and it's because my real function had this at the end:

> SET search_path FROM CURRENT;

Well, shame on you for omitting that critical detail from your example.

> which I never imagined would make any difference.

The reason SET options disable inlining is that inlining would leave
noplace for the SET to be applied and then removed.  In some cases it's
possible that we could prove that the SET need not occur at runtime,
but the inlining mechanism doesn't have that much knowledge about
configuration parameters.

> This still doesn't
> explain why it was being inlined sometimes - I didn't add and remove
> that bit, it was there the whole time!

That does seem improbable.  You sure you were looking at just one
function?

> Is there any reason this stuff isn't documented? It can have huge
> performance implications, so I'm surprised more people don't run into
> it.

There are a huge number of details of planner behavior that "can have
huge performance implications", and they change frequently.  It's not
clear to me that we can document this stuff in a way that's better than
referring to the source code.

            regards, tom lane

Of course, it seems silly now to not have included "SET search_path FROM current" in my post, but I had no idea what was and wasn't critical - that was the whole problem. Wisdom begins with knowing the right questions to ask! Yes, I was looking at the same function - even checked SVN logs to make sure and it had that "SET" for a long time. Still have no idea what happened there.

Re documentation - yes, the source code is well-commented, but I wouldn't have known where to look in the code if you hadn't told me. As a user, I didn't even know how to tell whether the function was being inlined or not. The EXPLAIN output just said "Function scan" - but it's always said that for me, so how was I to know what it should say? What about a section under chapter 14 (Performance Tips) on functions, which explains:

1) Why a function might run slower than running the same query directly. (Not inlined, plan caching, other reasons?)
2) How to tell whether a function is being inlined.
3) Things that would prevent a function from being inlined. I understand that some of these can change and ideally the doc should note which are inherent limitations and which are current implementation restrictions.

I'm going to write this up as a blog post just for my own reference, but nobody else is going to find it there. I'd love to see it documented properly by someone who knows what they're talking about.

Regards,

Evan

On 4/05/2012 1:30 AM, Tom Lane wrote:

Evan Martin <postgresql@realityexists.net> writes:

Thanks, I went into that function, added log statements everywhere and 
figured which check it's failing on:
!heap_attisnull(func_tuple, Anum_pg_proc_proconfig)
and it's because my real function had this at the end:

SET search_path FROM CURRENT;

Well, shame on you for omitting that critical detail from your example.

which I never imagined would make any difference.

The reason SET options disable inlining is that inlining would leave
noplace for the SET to be applied and then removed.  In some cases it's
possible that we could prove that the SET need not occur at runtime,
but the inlining mechanism doesn't have that much knowledge about
configuration parameters.

This still doesn't 
explain why it was being inlined sometimes - I didn't add and remove 
that bit, it was there the whole time!

That does seem improbable.  You sure you were looking at just one
function?

Is there any reason this stuff isn't documented? It can have huge 
performance implications, so I'm surprised more people don't run into 
it.

There are a huge number of details of planner behavior that "can have
huge performance implications", and they change frequently.  It's not
clear to me that we can document this stuff in a way that's better than
referring to the source code.
		regards, tom lane