| From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| 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-22 20:20:28 |
| Message-ID: | CAKFQuwYJbD2Xy+jNx30zdLmm7Xk6ZcP1_2N_hJqyqAcoYfB8SQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Wed, Sep 22, 2021 at 1:05 PM Israel Brewster <ijbrewster(at)alaska(dot)edu>
wrote:
> To work around the issue, I created a materialized view that I can update
> periodically, and of course I can query said view in no time flat. However,
> I’m concerned that as the dataset grows, the time it takes to refresh the
> view will also grow (correct me if I am wrong there).
>
I'd probably turn that index into a foreign key that just ensures that
every (station,channel) that appears in the data table also appears on the
lookup table. Grouping and array-ifying the lookup table would be
trivial. Either modify the application code or add a trigger to populate
the lookup table as needed.
The parentheses around channel in "array_agg(distinct(channel))" are
unnecessary - you are invoking composite-type syntax, which is ignored in
the single column case unless you write the optional ROW keyword, i.e.,
distinct ROW(channel)
David J.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Lewis | 2021-09-22 20:20:46 | Re: Faster distinct query? |
| Previous Message | Israel Brewster | 2021-09-22 20:05:22 | Faster distinct query? |