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
>