Re: Faster distinct query?

Поиск
Список
Период
Сортировка
От Geoff Winkless
Тема Re: Faster distinct query?
Дата
Msg-id CAEzk6fdsP_CfOe3bD3otw+J0rXBVQ2-Z+z_rKwnwbtpo9Fu_aQ@mail.gmail.com
обсуждение исходный текст
Ответ на Faster distinct query?  (Israel Brewster <ijbrewster@alaska.edu>)
Ответы Re: Faster distinct query?  (Israel Brewster <ijbrewster@alaska.edu>)
Список pgsql-general
Дерево обсуждения
On Wed, 22 Sept 2021 at 21:05, Israel Brewster <ijbrewster@alaska.edu> wrote:
I was wondering if there was any way to improve the performance of this query:

SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY station;

If you have tables of possible stations and channels (and if not, why not?), then an EXISTS query, something like

SELECT stations.name, ARRAY_AGG(channels.name)
  FROM stations, channels
  WHERE EXISTS
   (SELECT FROM data WHERE data.channels=channels.name AND data.station=stations.name)
GROUP BY stations.name

will usually be much faster, because it can stop scanning after the first match in the index.

Geoff

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

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: Faster distinct query?
Следующее
От: Jaime Solorzano
Дата:
Сообщение: Postgres incremental backups per db (not per cluster)