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