Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9DE3D2E2EEC for ; Thu, 31 Jan 2008 15:30:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54129-01-2 for ; Thu, 31 Jan 2008 15:30:43 -0400 (AST) Received: from smtp107.sbc.mail.re2.yahoo.com (smtp107.sbc.mail.re2.yahoo.com [68.142.229.98]) by postgresql.org (Postfix) with SMTP id AA5882E1714 for ; Thu, 31 Jan 2008 15:29:55 -0400 (AST) Received: (qmail 45940 invoked from network); 31 Jan 2008 19:29:54 -0000 Received: from unknown (HELO zeemac) (pankaj.garg@sbcglobal.net@24.6.87.239 with plain) by smtp107.sbc.mail.re2.yahoo.com with SMTP; 31 Jan 2008 19:29:53 -0000 X-YMail-OSG: WyzNwqsVM1lzdovFVzQl79_2dk8i8JOmywDTnVtJMFc.c6591bR6tl8iEPT9Y7DDU_iShQKgf1seRoeeKcNKYXKxId5db00w.lDQBG1AUKik1dVJJd.GQ6V5DxuAXcptdMVmlruwj.BKYpc- X-Yahoo-Newman-Property: ymail-3 Cc: pgsql-performance@postgresql.org Message-Id: From: Claire McLister To: Tom Lane In-Reply-To: <6915.1201553513@sss.pgh.pa.us> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v915) Subject: Re: JDBC/Stored procedure performance issue Date: Thu, 31 Jan 2008 11:29:52 -0800 References: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net> <6915.1201553513@sss.pgh.pa.us> X-Mailer: Apple Mail (2.915) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/302 X-Sequence-Number: 28541 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 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 majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly