Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 21AEFB5DBE9 for ; Wed, 31 Aug 2011 06:34:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 84527-01 for ; Wed, 31 Aug 2011 09:34:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og120.obsmtp.com (exprod7og120.obsmtp.com [64.18.2.18]) by mail.postgresql.org (Postfix) with ESMTP id AC791B5DBBE for ; Wed, 31 Aug 2011 06:34:38 -0300 (ADT) Received: from mail-ey0-f174.google.com ([209.85.215.174]) (using TLSv1) by exprod7ob120.postini.com ([64.18.6.12]) with SMTP ID DSNKTl4AKv2Rai57VCn3LfupkTQkevaHqpVR@postini.com; Wed, 31 Aug 2011 02:34:39 PDT Received: by eyx24 with SMTP id 24so401597eyx.19 for ; Wed, 31 Aug 2011 02:34:32 -0700 (PDT) Received: by 10.14.16.198 with SMTP id h46mr123430eeh.144.1314783272701; Wed, 31 Aug 2011 02:34:32 -0700 (PDT) Received: from [192.168.1.183] (dsl-hkibrasgw2-ff7ac300-240.dhcp.inet.fi [88.195.122.240]) by mx.google.com with ESMTPS id u56sm4453196eef.23.2011.08.31.02.34.29 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 31 Aug 2011 02:34:31 -0700 (PDT) Message-ID: <4E5E0024.9070801@enterprisedb.com> Date: Wed, 31 Aug 2011 12:34:28 +0300 From: Heikki Linnakangas User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI; rv:1.9.2.20) Gecko/20110820 Icedove/3.1.12 MIME-Version: 1.0 To: Jayadevan M CC: PGSQL Performance Subject: Re: Query performance issue References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201108/311 X-Sequence-Number: 44758 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