Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C282864FCEA for ; Sun, 30 Nov 2008 09:02:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64574-05 for ; Sun, 30 Nov 2008 09:02:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.185]) by mail.postgresql.org (Postfix) with ESMTP id 1BC2A64FCE3 for ; Sun, 30 Nov 2008 09:02:53 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id e30so2140475fke.3 for ; Sun, 30 Nov 2008 05:02:52 -0800 (PST) Received: by 10.187.210.3 with SMTP id m3mr2356286faq.59.1228050171991; Sun, 30 Nov 2008 05:02:51 -0800 (PST) Received: by 10.187.246.11 with HTTP; Sun, 30 Nov 2008 05:02:51 -0800 (PST) Message-ID: <4136ffa0811300502i2d8741fcn86a3a3efb281dd5c@mail.gmail.com> Date: Sun, 30 Nov 2008 14:02:51 +0100 From: "Greg Stark" To: Andrus Subject: Re: Increasing GROUP BY CHAR columns speed Cc: "Scott Carey" , "Scott Marlowe" , pgsql-performance@postgresql.org In-Reply-To: <926CBE5E333B42E79C82E5C50C51ACB8@andrusnotebook> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <3E5CE5640FFD4172956D223988D8D578@andrusnotebook> <926CBE5E333B42E79C82E5C50C51ACB8@andrusnotebook> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/302 X-Sequence-Number: 32033 On Sat, Nov 29, 2008 at 6:43 PM, Andrus wrote: >> I'm still not sure why the planner chose to sort rather than hash with >> oversized work_mem (is there an implied order in the query results I >> missed?). > > Group by contains decimal column exchrate. Maybe pg is not capable to use > hash with numeric datatype. It is in 8.3. I think sorting was improved dramatically since 8.1 as well. > I fixed this by adding cast to :::float > > bilkaib.exchrate:::float > > In this case query is much faster. > Hopefully this will not affect to result since numeric(13,8) can casted to > float without data loss. That's not true. Even pretty simple values like 1.1 cannot be represented precisely in a float. It would display properly though which might be all you're concerned with here. I'm not sure whether that's true for all values in numeric(13,8) though Do you really need to be grouping on so many columns? If they're normally all the same perhaps you can do two queries, one which fetches the common values without any group by, just a simple aggregate, and a second which groups by all these columns but only for the few exceptional records. You could avoid the collation support on the char() columns by casting them to bytea first. That might be a bit of a headache though. -- greg