Re: SQL functions not being inlined - Mailing list pgsql-general

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
>>
>