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?
|
| Список | 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.
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 по дате отправления: