Обсуждение: 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!

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