From: Greg Stark <stark@enterprisedb.com>
To: Andrus <kobruleht2@hot.ee>
Cc: Scott Carey <scott@richrelevance.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Increasing GROUP BY CHAR columns speed
Date: Sun, 30 Nov 2008 14:02:51 +0100
Message-ID: <4136ffa0811300502i2d8741fcn86a3a3efb281dd5c@mail.gmail.com> (raw)
In-Reply-To: <926CBE5E333B42E79C82E5C50C51ACB8@andrusnotebook>
References: <ggp1au$1iq2$1@news.hub.org>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9C9@EXVMBX018-1.exch018.msoutlookonline.net>
<3E5CE5640FFD4172956D223988D8D578@andrusnotebook>
<dcc563d10811281024l4cc3a063r4f17ba8337b1ee36@mail.gmail.com>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9CE@EXVMBX018-1.exch018.msoutlookonline.net>
<926CBE5E333B42E79C82E5C50C51ACB8@andrusnotebook>
On Sat, Nov 29, 2008 at 6:43 PM, Andrus <kobruleht2@hot.ee> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, kobruleht2@hot.ee, scott@richrelevance.com, scott.marlowe@gmail.com
Subject: Re: Increasing GROUP BY CHAR columns speed
In-Reply-To: <4136ffa0811300502i2d8741fcn86a3a3efb281dd5c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox