On Sat, Nov 29, 2008 at 6:43 PM, Andrus <kobruleht2@xxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
