Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 13974B5DBE9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 31 Aug 2011 06:37:41 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 88908-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 31 Aug 2011 09:37:34 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hellskitchen.mr.itd.umich.edu (smtp.mail.umich.edu
	[141.211.14.82])
	by mail.postgresql.org (Postfix) with ESMTP id 776ADB5DBE8
	for <pgsql-performance@postgresql.org>;
	Wed, 31 Aug 2011 06:37:34 -0300 (ADT)
Received: FROM [192.168.0.101] (Mismatch [122.167.100.36])
	By hellskitchen.mr.itd.umich.edu ID 4E5E00D6.2B507.23926 ;
	Authuser sushant; 31 Aug 2011 05:37:27 EDT
Subject: Re: Query performance issue
From: Sushant Sinha <sushant354@gmail.com>
Reply-To: sushant354@gmail.com
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
In-Reply-To: 
 <OF6B813C80.12B50253-ON652578FD.00310FC3-652578FD.00317C74@ibsplc.com>
References: 
 <OF6B813C80.12B50253-ON652578FD.00310FC3-652578FD.00317C74@ibsplc.com>
Content-Type: text/plain; charset="UTF-8"
Date: Wed, 31 Aug 2011 15:07:20 +0530
Message-ID: <1314783440.1793.0.camel@dragflick>
Mime-Version: 1.0
X-Mailer: Evolution 2.32.2 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.798 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001,
	UNPARSEABLE_RELAY=0.001
X-Spam-Level: 
X-Archive-Number: 201108/312
X-Sequence-Number: 44759

Where is the query? And also paste the \d to show the tables and
indexes.

-Sushant.

On Wed, 2011-08-31 at 14:30 +0530, Jayadevan M wrote:
> Hello all, 
> I have a query which takes about 20 minutes to execute and retrieves
> 2000-odd records. The explain for the query is pasted here 
> http://explain.depesz.com/s/52f 
> The same query, with similar data structures/indexes and data comes
> back in 50 seconds in Oracle. We just ported the product to PostgreSQL
> and are testing it. Any input on what to look for? 
> 
> Possible relevant parameters are 
> shared_buffers = 4GB                     
> temp_buffers = 8MB                     
> work_mem = 96MB                       
> maintenance_work_mem = 1GB             
> effective_cache_size = 8GB             
> default_statistics_target = 50         
> 
> It is a machine with 16 GB RAM. 
> Regards, 
> Jayadevan
> 
> 
> 
> 
> 
> DISCLAIMER: 
> 
> "The information in this e-mail and any attachment is intended only
> for the person to whom it is addressed and may contain confidential
> and/or privileged material. If you have received this e-mail in error,
> kindly contact the sender and destroy all copies of the original
> communication. IBS makes no warranty, express or implied, nor
> guarantees the accuracy, adequacy or completeness of the information
> contained in this email or any attachment and is not liable for any
> errors, defects, omissions, viruses or for resultant loss or damage,
> if any, direct or indirect."
> 
> 
> 
>