function runs slow

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



_________________________________________________________________

Responses

Browse pgsql-novice by date

  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?