Skip site navigation (1) Skip section navigation (2)

Re: JDBC/Stored procedure performance issue

From: Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: mclister(at)zeesource(dot)net, pgsql-performance(at)postgresql(dot)org
Subject: Re: JDBC/Stored procedure performance issue
Date: 2008-01-29 10:12:49
Message-ID: 479EFC21.60606@rwa-net.co.uk (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)zeesource(dot)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(at)postgresql(dot)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
>   

In response to

Responses

pgsql-performance by date

Next:From: Arjen van der MeijdenDate: 2008-01-29 10:29:23
Subject: Re: 8x2.5" or 6x3.5" disks
Previous:From: davidDate: 2008-01-29 08:32:25
Subject: Re: 8x2.5" or 6x3.5" disks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group