Re: Increasing GROUP BY CHAR columns speed

От: Andrus
Тема: Re: Increasing GROUP BY CHAR columns speed
Дата: ,
Msg-id: 926CBE5E333B42E79C82E5C50C51ACB8@andrusnotebook
(см: обсуждение, исходный текст)
Ответ на: Re: Increasing GROUP BY CHAR columns speed  (Scott Carey)
Ответы: Re: Increasing GROUP BY CHAR columns speed  ("Greg Stark")
Список: pgsql-performance

Скрыть дерево обсуждения

Increasing GROUP BY CHAR columns speed  ("Andrus", )
 Re: Increasing GROUP BY CHAR columns speed  (Scott Carey, )
  Re: Increasing GROUP BY CHAR columns speed  ("Andrus", )
   Re: Increasing GROUP BY CHAR columns speed  ("Scott Marlowe", )
    Re: Increasing GROUP BY CHAR columns speed  (Scott Carey, )
     Re: Increasing GROUP BY CHAR columns speed  ("Andrus", )
     Re: Increasing GROUP BY CHAR columns speed  ("Andrus", )
     Re: Increasing GROUP BY CHAR columns speed  ("Scott Marlowe", )
     Re: Increasing GROUP BY CHAR columns speed  ("Andrus", )
      Re: Increasing GROUP BY CHAR columns speed  ("Greg Stark", )
 Re: Increasing GROUP BY CHAR columns speed  ("Andrus", )

>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.



В списке pgsql-performance по дате сообщения:

От: "Andrus"
Дата:
Сообщение: Re: Increasing GROUP BY CHAR columns speed
От: "Greg Stark"
Дата:
Сообщение: Re: Increasing GROUP BY CHAR columns speed