Re: Faster distinct query?

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