Re: [PERFORM] JDBC/Stored procedure performance issue
Thanks, Tom. Looks like that was the issue. I changed the function to use groupid = 57925 instead of groupid = $1 (I can do the same change in the JDBC prepare statement), and the performance is much better. It is still more than twice that of the simple query: 401.111 ms vs. 155.544 ms, which, however, is more acceptable than 3000ms. Will upgrade to 8.1 at some point, but would like to get reasonable performance with 7.4 until then. I did increase the statistics target to 1000. Claire On Jan 28, 2008, at 12:51 PM, Tom Lane wrote: Claire McLister <[EMAIL PROTECTED]> writes: When I do an EXPLAIN ANALYZE on one query that returns 3261 rows, it executes in a reasonable 159ms: ... If I issue the same query over JDBC or use a PSQL stored procedure, it takes over 3000 ms, which, of course is unacceptable! I suspect that the problem is with "groupid = $1" instead of "groupid = 57925". The planner is probably avoiding an indexscan in the parameterized case because it's guessing the actual value will match so many rows as to make a seqscan faster. Is the distribution of groupid highly skewed? You might get better results if you increase the statistics target for that column. Switching to something newer than 7.4.x might help too. 8.1 and up support "bitmap" indexscans which work much better for large numbers of hits, and correspondingly the planner will use one in cases where it wouldn't use a plain indexscan. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] JDBC/Stored procedure performance issue
Matthew Lunnon <[EMAIL PROTECTED]> writes: > Is there any way to work out what plan the query is using in side the > function? I think I have a similar problem with a query taking much > longer from inside a function than it does as a select statement. Standard approach is to PREPARE a statement that has parameters in the same places where the function uses variables/parameters, and then use EXPLAIN [ANALYZE] EXECUTE to test it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] JDBC/Stored procedure performance issue
Hi Tom, Is there any way to work out what plan the query is using in side the function? I think I have a similar problem with a query taking much longer from inside a function than it does as a select statement. Regards Matthew Tom Lane wrote: Claire McLister <[EMAIL PROTECTED]> writes: When I do an EXPLAIN ANALYZE on one query that returns 3261 rows, it executes in a reasonable 159ms: ... If I issue the same query over JDBC or use a PSQL stored procedure, it takes over 3000 ms, which, of course is unacceptable! I suspect that the problem is with "groupid = $1" instead of "groupid = 57925". The planner is probably avoiding an indexscan in the parameterized case because it's guessing the actual value will match so many rows as to make a seqscan faster. Is the distribution of groupid highly skewed? You might get better results if you increase the statistics target for that column. Switching to something newer than 7.4.x might help too. 8.1 and up support "bitmap" indexscans which work much better for large numbers of hits, and correspondingly the planner will use one in cases where it wouldn't use a plain indexscan. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly _ This e-mail has been scanned for viruses by Verizon Business Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.verizonbusiness.com/uk
Re: [PERFORM] JDBC/Stored procedure performance issue
Claire McLister <[EMAIL PROTECTED]> writes: > When I do an EXPLAIN ANALYZE on one query that returns 3261 rows, it > executes in a reasonable 159ms: > ... > If I issue the same query over JDBC or use a PSQL stored procedure, it > takes over 3000 ms, which, of course is unacceptable! I suspect that the problem is with "groupid = $1" instead of "groupid = 57925". The planner is probably avoiding an indexscan in the parameterized case because it's guessing the actual value will match so many rows as to make a seqscan faster. Is the distribution of groupid highly skewed? You might get better results if you increase the statistics target for that column. Switching to something newer than 7.4.x might help too. 8.1 and up support "bitmap" indexscans which work much better for large numbers of hits, and correspondingly the planner will use one in cases where it wouldn't use a plain indexscan. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] JDBC/Stored procedure performance issue
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" @
'010320E610010005F05EC000A0874C424000F05EC000406D7D444000885DC000406D7D444000885DC000A0874C424000F05EC000A0874C4240
'::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[EMAIL PROTECTED]
21060 Homestead Road Suite 150
Cupertino, CA 95014408-733-2737(fax)
http://www.zeemaps.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
