On Wed, 22 Sept 2021 at 21:05, Israel Brewster <ijbrews...@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
