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

Re: Query performance issue

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
Cc: PGSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query performance issue
Date: 2011-08-31 09:34:28
Message-ID: 4E5E0024.9070801@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 31.08.2011 12:00, 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.

Please run EXPLAIN ANALYZE on the query and post that, it's hard to say 
what's wrong from just the query plan, without knowing where the time is 
actually spent. And the schema of the tables involved, and any indexes 
on them. (see also http://wiki.postgresql.org/wiki/SlowQueryQuestions)

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-performance by date

Next:From: Sushant SinhaDate: 2011-08-31 09:37:20
Subject: Re: Query performance issue
Previous:From: Jayadevan MDate: 2011-08-31 09:00:31
Subject: Query performance issue

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