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@xxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
