Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 10E0F2E01FD
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 29 Jan 2008 06:17:09 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 66929-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 29 Jan 2008 06:17:01 -0400 (AST)
Received: from mail-relay.rwa-net.co.uk (mail-relay.rwa-net.co.uk
	[62.189.139.18])
	by postgresql.org (Postfix) with ESMTP id 921082E026D
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jan 2008 06:12:52 -0400 (AST)
Received: from saturn.rwa-net.co.uk (saturn.rwa-net.co.uk [62.189.139.33])
	by mail-relay.rwa-net.co.uk (Postfix) with ESMTP id 7F54A7D6357
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jan 2008 09:51:49 +0000 (GMT)
Received: from [127.0.0.1] by saturn.rwa-net.co.uk (GMS
	14.01.3539/NT4698.00.83343e8d) with ESMTP id awfkgaaa for
	pgsql-performance@postgresql.org; Tue, 29 Jan 2008 10:12:49 +0000
Message-ID: <479EFC21.60606@rwa-net.co.uk>
Date: Tue, 29 Jan 2008 10:12:49 +0000
From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
Reply-To: mlunnon@rwa-net.co.uk
Organization: RWA
User-Agent: Thunderbird 1.5.0.13 (Windows/20070809)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: mclister@zeesource.net,  pgsql-performance@postgresql.org
Subject: Re: JDBC/Stored procedure performance issue
References: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net>
	<6915.1201553513@sss.pgh.pa.us>
In-Reply-To: <6915.1201553513@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------050400040109080808070706"
X-AuthenticatedSender: mlunnon@rwa-net.co.uk
X-Info: UNKNOWN-USER
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/281
X-Sequence-Number: 28520

This is a multi-part message in MIME format.
--------------050400040109080808070706
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 <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
>
> _____________________________________________________________________
> 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
>   

--------------050400040109080808070706
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Tom,<br>
<br>
Is there any way to work out what plan the query is using in side the
function?&nbsp; I think I have a similar problem with a query taking much
longer from inside a function than it does as a select statement.<br>
<br>
Regards<br>
Matthew<br>
<br>
Tom Lane wrote:
<blockquote cite="mid6915.1201553513@sss.pgh.pa.us" type="cite">
  <pre wrap="">Claire McLister <a class="moz-txt-link-rfc2396E" href="mailto:mclister@zeesource.net">&lt;mclister@zeesource.net&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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!
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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 <a class="moz-txt-link-abbreviated" href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> 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 <a class="moz-txt-link-freetext" href="http://www.verizonbusiness.com/uk">http://www.verizonbusiness.com/uk</a>
  </pre>
</blockquote>
</body>
</html>

--------------050400040109080808070706--