Search Postgresql Archives

Re: SQL functions not being inlined

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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


[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]

Add to Google Powered by Linux