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
Turn down-time into play-time with Messenger games
Play Now!
