From: | Charles Holleran <scorpdaddy(at)hotmail(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | function runs slow |
Date: | 2010-07-15 05:31:23 |
Message-ID: | BAY126-W20926F93D399CB24759AD4CEBB0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have a query:
SELECT date_observed FROM tbl_a
WHERE
x = 384394918 AND
y = 5 AND
date_observed <= '14-Jul-10 00:00'
ORDER BY
date_observed DESC
LIMIT
1;
This query returns in 16 ms. Great! So I functionized the working method as:
CREATE OR REPLACE FUNCTION get_last_on_or_before(c integer, t integer, g timestamp with time zone)
RETURNS timestamp with time zone AS
$BODY$
SELECT date_observed FROM tbl_a
WHERE
x = $1 AND
y = $2 AND
date_observed <= $3
ORDER BY
date_observed DESC
LIMIT
1;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;
Then I queried this new function:
SELECT get_last_on_or_before(384394918, 5, '14-Jul-10 00:00');
The query returns in 2891 ms! This is too slow for the application.
Why is it slow when the same un-functionized query was fast? The tbl_a has est. 30,000,000 records. It has an index for (x,y) pairs. It has an index for date_observed. So good results on the straight query. Where did I go wrong with the function?
Charlie
_________________________________________________________________
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-07-15 05:47:59 | Re: function runs slow |
Previous Message | Mark Kelly | 2010-07-14 19:13:20 | Re: Order by provided IDs? |