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