From: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
---|---|
To: | Israel Brewster <ijbrewster(at)alaska(dot)edu> |
Cc: | PostgreSQL Mailing Lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Faster distinct query? |
Date: | 2021-09-23 18:36:48 |
Message-ID: | CAEzk6fdsP_CfOe3bD3otw+J0rXBVQ2-Z+z_rKwnwbtpo9Fu_aQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 22 Sept 2021 at 21:05, Israel Brewster <ijbrewster(at)alaska(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Solorzano | 2021-09-23 19:57:27 | Postgres incremental backups per db (not per cluster) |
Previous Message | Rob Sargent | 2021-09-23 17:08:38 | Re: Faster distinct query? |