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_timesliceWHERE 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
[Postgresql Jobs] [Postgresql Admin] [Postgresql Performance] [Linux Clusters] [PHP Home] [PHP on Windows] [Programming PHP] [Kernel Newbies] [PHP Classes] [Find Someone Nice] [PHP Books] [PHP Databases] [Postgresql & PHP] [Yosemite]