From: Claire McLister <mclister@zeesource.net>
To: pgsql-performance@postgresql.org
Subject: JDBC/Stored procedure performance issue
Date: Mon, 28 Jan 2008 09:59:07 -0800
Message-ID: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net> (raw)
Hi All,
I am experiencing a strange performance issue with Postgresql (7.4.19)
+ PostGIS. (I posted to the PostGIS list but got no response, so am
trying here.)
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@zeesource.net
21060 Homestead Road Suite 150
Cupertino, CA 95014 408-733-2737(fax)
http://www.zeemaps.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mclister@zeesource.net
Subject: Re: JDBC/Stored procedure performance issue
In-Reply-To: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox