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
