Re: Huge Data sets, simple queries

Список
Период
Сортировка
От Tom Lane
Тема Re: Huge Data sets, simple queries
Дата
Msg-id 11814.1138463702@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Huge Data sets, simple queries  ("Mike Biamonte")
Ответы Re: Huge Data sets, simple queries  ("Jeffrey W. Baker")
Список pgsql-performance
Дерево обсуждения
Huge Data sets, simple queries  ("Mike Biamonte", )
 Re: Huge Data sets, simple queries  ("Jeffrey W. Baker", )
 Re: Huge Data sets, simple queries  (Tom Lane, )
  Re: Huge Data sets, simple queries  ("Jeffrey W. Baker", )
   Re: Huge Data sets, simple queries  (Tom Lane, )
    Re: Huge Data sets, simple queries  (Tom Lane, )
 Re: Huge Data sets, simple queries  ("Luke Lonergan", )
  Re: Huge Data sets, simple queries  (hubert depesz lubaczewski, )
   Re: Huge Data sets, simple queries  (Michael Stone, )
  Re: Huge Data sets, simple queries  ("Jim C. Nasby", )
   Re: Huge Data sets, simple queries  ("Luke Lonergan", )
    Re: Huge Data sets, simple queries  (Kevin, )
    Re: Huge Data sets, simple queries  ("Jim C. Nasby", )
     Re: Huge Data sets, simple queries  ("Luke Lonergan", )
      Re: Huge Data sets, simple queries  ("Jim C. Nasby", )
       Re: Huge Data sets, simple queries  ("Luke Lonergan", )
        Re: Huge Data sets, simple queries  ("Jim C. Nasby", )
    Re: Huge Data sets, simple queries  ("Jeffrey W. Baker", )
     Re: Huge Data sets, simple queries  ("Luke Lonergan", )
      Re: Huge Data sets, simple queries  (PFC, )
       Re: Huge Data sets, simple queries  ("Luke Lonergan", )
      Re: Huge Data sets, simple queries  ("Steinar H. Gunderson", )
       Re: Huge Data sets, simple queries  ("Luke Lonergan", )
      Re: Huge Data sets, simple queries  ("Jeffrey W. Baker", )
       Re: Huge Data sets, simple queries  ("Luke Lonergan", )
        Re: Huge Data sets, simple queries  ("Jeffrey W. Baker", )
         Re: Huge Data sets, simple queries  (PFC, )
          Re: Huge Data sets, simple queries  ("Luke Lonergan", )
           Re: Huge Data sets, simple queries  ("Steinar H. Gunderson", )
           Re: Huge Data sets, simple queries  (Mike Rylander, )
         Re: Huge Data sets, simple queries  ("Luke Lonergan", )
       Re: Huge Data sets, simple queries  (Michael Stone, )
     Re: Huge Data sets, simple queries  (Alan Stange, )
 Re: Huge Data sets, simple queries  ("Luke Lonergan", )
  Re: Huge Data sets, simple queries  ("Jeffrey W. Baker", )
  Re: Huge Data sets, simple queries  (Charles Sprickman, )
   Re: Huge Data sets, simple queries  ("Luke Lonergan", )
  Re: Huge Data sets, simple queries  (hubert depesz lubaczewski, )
   Re: Huge Data sets, simple queries  ("Luke Lonergan", )
 Re: Huge Data sets, simple queries  (Michael Adler, )
 Re: Huge Data sets, simple queries  ("Craig A. James", )
"Mike Biamonte" <> writes:
> The queries I need to run on my 200 million transactions are relatively
> simple:

>    select month, count(distinct(cardnum)) count(*), sum(amount) from
> transactions group by month;

count(distinct) is not "relatively simple", and the current
implementation isn't especially efficient.  Can you avoid that
construct?

Assuming that "month" means what it sounds like, the above would result
in running twelve parallel sort/uniq operations, one for each month
grouping, to eliminate duplicates before counting.  You've got sortmem
set high enough to blow out RAM in that scenario ...

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Huge Data sets, simple queries
Следующее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Huge Data sets, simple queries