From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: mclister@zeesource.net
Cc: pgsql-performance@postgresql.org
Subject: Re: JDBC/Stored procedure performance issue
Date: Tue, 29 Jan 2008 10:12:49 +0000
Message-ID: <479EFC21.60606@rwa-net.co.uk> (raw)
In-Reply-To: <6915.1201553513@sss.pgh.pa.us>
References: <018573B2-DC52-4B98-9AB6-1ACB953B0035@zeesource.net>
<6915.1201553513@sss.pgh.pa.us>
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
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mlunnon@rwa-net.co.uk, tgl@sss.pgh.pa.us, mclister@zeesource.net
Subject: Re: JDBC/Stored procedure performance issue
In-Reply-To: <479EFC21.60606@rwa-net.co.uk>
* 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