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

Re: Increasing GROUP BY CHAR columns speed

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "Scott Carey" <scott(at)richrelevance(dot)com>,"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Increasing GROUP BY CHAR columns speed
Date: 2008-11-29 17:43:50
Message-ID: 926CBE5E333B42E79C82E5C50C51ACB8@andrusnotebook (view raw or flat)
Thread:
Lists: pgsql-performance
>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.

>   My guess is that this query can still get much faster if a hash is 
> possible on the last part.  It looks like the gain so far has more to do 
> with sorting purely in memory which reduced the number of compares 
> required.  But that is just a guess.

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.

Andrus. 


In response to

Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2008-11-30 13:02:51
Subject: Re: Increasing GROUP BY CHAR columns speed
Previous:From: Kevin GrittnerDate: 2008-11-28 22:28:42
Subject: Re: Memory Allocation

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