Re: Faster distinct query?

On Wed, Sep 22, 2021 at 1:05 PM Israel Brewster <ijbrewster@alaska.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.