SQL functions not being inlined - Mailing list pgsql-general

From Evan Martin
Subject SQL functions not being inlined
Date
Msg-id 4FA09F47.50302@realityexists.net
Whole thread Raw
Responses Re: SQL functions not being inlined
Re: SQL functions not being inlined
List pgsql-general
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

pgsql-general by date:

Previous
From: Misa Simic
Date:
Subject: Re: How do I setup this Exclusion Constraint?
Next
From: Chris Angelico
Date:
Subject: Re: SQL functions not being inlined