Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 969311337C37 for ; Thu, 16 Sep 2010 02:39:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 96234-02 for ; Thu, 16 Sep 2010 05:38:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailout.thl.fi (mailout.thl.fi [193.167.195.59]) by mail.postgresql.org (Postfix) with ESMTP id CC7F61337C22 for ; Thu, 16 Sep 2010 02:38:53 -0300 (ADT) Received: from mailout (localhost.localdomain [127.0.0.1]) by mailout.thl.fi (8.13.1/8.13.1) with SMTP id o8G5cqqx003057; Thu, 16 Sep 2010 08:38:52 +0300 Received: from 172.24.32.92 (172.24.32.92) by mailout (F-Secure/fsigk_smtp/580/mailout); Thu, 16 Sep 2010 08:38:52 +0200 (EET) X-Virus-Status: clean(F-Secure/fsigk_smtp/580/mailout) Received: from exhub1.stakes.fi (exhub1.stakes.fi [10.10.3.203]) by mail-hub.thl.fi (8.13.8/8.13.8) with ESMTP id o8G5cqPq029429; Thu, 16 Sep 2010 08:38:52 +0300 Received: from [172.24.70.235] (172.24.70.235) by exhub1.stakes.fi (10.10.3.203) with Microsoft SMTP Server id 8.1.375.2; Thu, 16 Sep 2010 08:38:51 +0300 Message-ID: <4C91B057.2010106@thl.fi> Date: Thu, 16 Sep 2010 08:51:19 +0300 From: =?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.12) Gecko/20100826 Thunderbird/3.0.7 MIME-Version: 1.0 To: Merlin Moncure CC: "pgsql-performance@postgresql.org" , Tom Lane Subject: Re: Performance problem with joined aggregate query References: <4C906706.40108@thl.fi> 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.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/104 X-Sequence-Number: 40307 On 09/16/2010 01:25 AM, Merlin Moncure wrote: > Take a look at this, and the responses. Is it the same case?: > http://www.mail-archive.com/pgsql-performance@postgresql.org/msg21756.html > > merlin > Yes, looks like this is the same case. This makes it hard to use views having group by in them, as the whole group by part will always be executed. Back to planning board then... I guess my possibilities for pivot views are: - crosstab: Will make statistics go "bad", that is, the crosstab query will always seem to return static number of rows. This can cause problems in complex queries using the view. IIRC performance is a bit worse than pivot by group by. - left joins: if joining the same table 20 times, there will be some planner overhead. Maybe the best way for my usage case. Also about 2x slower than pivot using group by. - subselect each of the columns: way worse performance: for my use case, each added column adds about 50ms to run time, so for 20 columns this will take 1 second. The group by pivot query runs in 250ms. Any other ideas? - Anssi