Re: Faster distinct query?

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Faster distinct query?
Дата
Msg-id CAHOFxGrvxWFTxNQZNe-cWgf__5ErEZ5JUoFzHNXGGnK9=oqX-A@mail.gmail.com
обсуждение исходный текст
Ответ на Faster distinct query?  (Israel Brewster <ijbrewster@alaska.edu>)
Ответы Re: Faster distinct query?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Faster distinct query?  (David Rowley <dgrowleyml@gmail.com>)
Список pgsql-general
In the future, please share the plan returned by explain analyze, and some data about how many rows in the involved tables, what type of system you are running it on, any changes from default configs, etc.

How does the below work? It should do a very simple index scan only, then aggregate the relative few rows after the fact.

select station, array_agg(distinct(channel)) as channels
FROM(
SELECT station,channel FROM data GROUP BY station,channel
) AS sub
group by station;

If there is correlation between station & channel, then you might look at creating a multivariate statistics object and analyzing the table so the planner can make better choices, knowing that channel is dependent on station perhaps. I wouldn't necessarily think that it would help this query, but perhaps others. Also, you might try creating only dependencies, only ndistinct type, or some combination other than all 3 types.

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

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Faster distinct query?
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Faster distinct query?