Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 860F8B5DBD8; Wed, 31 Aug 2011 08:42:04 -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 56099-03; Wed, 31 Aug 2011 11:41:57 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id 0CF7AB5DBCE; Wed, 31 Aug 2011 08:41:56 -0300 (ADT) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 8EA0D15E411C; Wed, 31 Aug 2011 13:41:55 +0200 (CEST) Received: from 85.162.50.94 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Wed, 31 Aug 2011 13:41:55 +0200 Message-ID: <30d4c8e39eef1ff30337242b4dedfd34.squirrel@sq.gransy.com> In-Reply-To: References: <4E5E0024.9070801@enterprisedb.com> Date: Wed, 31 Aug 2011 13:41:55 +0200 Subject: Re: Query performance issue From: "Tomas Vondra" To: "Jayadevan M" Cc: "Heikki Linnakangas" , "PGSQL Performance" , pgsql-performance-owner@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal 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/317 X-Sequence-Number: 44764 On 31 Srpen 2011, 13:19, Jayadevan M wrote: > Hello, > >> > >> > 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. >> Here is the explain analyze >> http://explain.depesz.com/s/MY1 > Going through the url tells me that statistics may be off. I will try > analyzing the tables. That should help? > Regards, > Jayadevan That could help, but not necessarily. A really interesting part is the sort near the bottom - -> Sort (cost=1895.95..1896.49 rows=215 width=61) (actual time=25.926..711784.723 rows=2673340321 loops=1) Sort Key: memmst.memshpsta Sort Method: quicksort Memory: 206kB -> Nested Loop (cost=0.01..1887.62 rows=215 width=61) (actual time=0.088..23.445 rows=1121 loops=1) How can a sort ge 1121 rows at the input and return 2673340321 rows at the output? Not sure where this comes from. BTW what PostgreSQL version is this? Tomas