JDBC/Stored procedure performance issue
От | Claire McLister |
---|---|
Тема | JDBC/Stored procedure performance issue |
Дата | |
Msg-id | 018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net обсуждение исходный текст |
Ответы |
Re: JDBC/Stored procedure performance issue
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: