pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Claire McLister <mclister@zeesource.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: JDBC/Stored procedure performance issue
Date: Thu, 31 Jan 2008 11:29:52 -0800
Message-ID: <B38E0992-43DA-4F9E-ADF9-10D2CD9A03F2@zeesource.net> (raw)
In-Reply-To: <6915.1201553513@sss.pgh.pa.us>
References: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net>
	<6915.1201553513@sss.pgh.pa.us>

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 <mclister@zeesource.net> 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




view thread (5+ messages)

Message-ID: <B38E0992-43DA-4F9E-ADF9-10D2CD9A03F2@zeesource.net>
Permalink:  ../B38E0992-43DA-4F9E-ADF9-10D2CD9A03F2@zeesource.net/
Also on:    postgresql.org/message-id/B38E0992-43DA-4F9E-ADF9-10D2CD9A03F2@zeesource.net

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mclister@zeesource.net, tgl@sss.pgh.pa.us
  Subject: Re: JDBC/Stored procedure performance issue
  In-Reply-To: <B38E0992-43DA-4F9E-ADF9-10D2CD9A03F2@zeesource.net>
  References: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net> <6915.1201553513@sss.pgh.pa.us> <B38E0992-43DA-4F9E-ADF9-10D2CD9A03F2@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