Skip site navigation (1) Skip section navigation (2)

Re: Increasing GROUP BY CHAR columns speed

From: "Greg Stark" <stark(at)enterprisedb(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: "Scott Carey" <scott(at)richrelevance(dot)com>, "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Increasing GROUP BY CHAR columns speed
Date: 2008-11-30 13:02:51
Message-ID: 4136ffa0811300502i2d8741fcn86a3a3efb281dd5c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, Nov 29, 2008 at 6:43 PM, Andrus <kobruleht2(at)hot(dot)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

In response to

pgsql-performance by date

Next:From: tmpDate: 2008-11-30 18:45:11
Subject: Query optimization
Previous:From: AndrusDate: 2008-11-29 17:43:50
Subject: Re: Increasing GROUP BY CHAR columns speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group