[postgis-users] Strange performance issue

[Claire McLister]

Hi All,

  I am experiencing a strange performance issue.

  We have a table of entries that contains latitude, longitude values  
and I have a simple query to retrieve all entries within a specified 2- 
D box.

  The latitude, longitude are stored as decimals, plus a trigger  
stores the corresponding geometry object.

  When I do an EXPLAIN ANALYZE on one query that returns 3261 rows, it  
executes in a reasonable 159ms:

  EXPLAIN ANALYZE SELECT DISTINCT latitude, longitude, color FROM  
NewEntries
	 WHERE groupid = 57925 AND
		       location @ SetSRID(MakeBox2D(SetSRID(MakePoint(-123.75,  
36.597889), 4326),
                 		                    SetSRID(MakePoint(-118.125,  
40.979898), 4326)), 4326);


  Unique  (cost=23.73..23.74 rows=1 width=30) (actual  
time=143.648..156.081 rows=3261 loops=1)
    ->  Sort  (cost=23.73..23.73 rows=1 width=30) (actual  
time=143.640..146.214 rows=3369 loops=1)
          Sort Key: latitude, longitude, color
          ->  Index Scan using group_index on newentries   
(cost=0.00..23.72 rows=1 width=30) (actual time=0.184..109.346  
rows=3369 loops=1)
                Index Cond: (groupid = 57925)
                Filter: ("location" @  
'0103000020E610000001000000050000000000000000F05EC0000000A0874C42400000000000F05EC0000000406D7D44400000000000885DC0000000406D7D44400000000000885DC0000000A0874C42400000000000F05EC0000000A0874C4240 
'::geometry)
  Total runtime: 159.430 ms
(7 rows)

  If I issue the same query over JDBC or use a PSQL stored procedure,  
it takes over 3000 ms, which, of course is unacceptable!

  Function Scan on gettilelocations  (cost=0.00..12.50 rows=1000  
width=30) (actual time=3311.368..3319.265 rows=3261 loops=1)
  Total runtime: 3322.529 ms
(2 rows)

  The function gettilelocations is defined as:

CREATE OR REPLACE FUNCTION GetTileLocations(Integer, real, real, real,  
real)
     RETURNS SETOF TileLocation
  AS
'
     DECLARE
         R TileLocation;
     BEGIN
         FOR R IN SELECT DISTINCT latitude, longitude, color FROM  
NewEntries
             WHERE groupid = $1 AND
                 location @ SetSRID(MakeBox2D(SetSRID(MakePoint($2,  
$3), 4326),
                                      SetSRID(MakePoint($4, $5), 4326)),
                    4326) LOOP
             RETURN NEXT R;
         END LOOP;
     RETURN;
     END;
'
  LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT;

  Can someone please tell me what we are doing wrong? Any help would  
be greatly appreciated.

  Thanks

Claire

  --
  Claire McLister                        mclister at zeesource.net
  21060 Homestead Road Suite 150
  Cupertino, CA 95014            408-733-2737(fax)

                      http://www.zeemaps.com