Обсуждение: function runs slow
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!
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!
In response to Charles Holleran : > 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? The planner isn't able to choose the right plan because he don't know the actual parameters. You can do EXECUTE 'string that contains your query' to enforce re-planning the query. http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
Yep. That was it. Thanks so much. Charlie -----Original Message----- From: "A. Kretschmer" <andreas.kretschmer@schollglas.com> Date: Thu, 15 Jul 2010 05:47:59 To: <pgsql-novice@postgresql.org> Subject: Re: [NOVICE] function runs slow In response to Charles Holleran : > 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? The planner isn't able to choose the right plan because he don't know the actual parameters. You can do EXECUTE 'string that contains your query' to enforce re-planning the query. http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
A. Kretschmer wrote: > In response to Charles Holleran : > >> 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? >> > > The planner isn't able to choose the right plan because he don't know > the actual parameters. > That is precisely why I asked about the values for the bind variables. Unfortunately, there is no facility that could influence the plan in this case. Constant re-parsing will, of course, be grossly suboptimal. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com