Message-ID: <438E4F12.7060205@telogis.com>
Date: Thu, 01 Dec 2005 14:17:06 +1300
From: Ralph Mason <ralph.mason@telogis.com>
User-Agent: Thunderbird 1.4 (Windows/20050908)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Query is 800 times slower when running in function! 
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have a simple query that is running inside a plpgsql function.

SELECT INTO _point_id id FROM ot2.point WHERE unit_id = _unit_id AND 
time > _last_status ORDER BY time LIMIT 1;
 
Both _unit_id and _last_status variables in the function. the table has 
an index on unit_id,point

When this runs inside a function it is taking about 800ms.  When I run 
it stand alone it takes about .8 ms, which is a big difference.

I can find no reason for this. I have checked that time and _last_status 
time are both timestamps and unit_id and _unit_id are both oids.

The explain looks perfect

 explain select id from point where unit_id = 95656 and time > 
'2005-11-30 23:11:00' order by time limit 1;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9.94..9.95 rows=1 width=12)
   ->  Sort  (cost=9.94..9.95 rows=2 width=12)
         Sort Key: "time"
         ->  Index Scan using unit_point on point  (cost=0.00..9.93 
rows=2 width=12)
               Index Cond: ((unit_id = 95656::oid) AND ("time" > 
'2005-11-30 23:11:00'::timestamp without time zone))
(5 rows)

Time: 0.731 ms

A query inside the same function that runs right before this one runs at 
the expected speed (about 1 ms)

SELECT INTO _last_status time FROM ot2.point  WHERE unit_id = _unit_id 
AND flags & 64 = 64 ORDER BY unit_id desc, time DESC LIMIT 1;

It uses the same table and indexes.

To time individual queries inside the function I am using:

tt := (timeofday()::timestamp)-startt;  RAISE INFO 'Location A %' , tt; 
startt := timeofday()::timestamp;

tt is an interval and startt is a timestamp. 


I am out of things to try. Can anyone help?

Thanks
Ralph