Re: Faster distinct query?
| От | David G. Johnston |
|---|---|
| Тема | Re: Faster distinct query? |
| Дата | |
| Msg-id | CAKFQuwYJbD2Xy+jNx30zdLmm7Xk6ZcP1_2N_hJqyqAcoYfB8SQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Faster distinct query? (Israel Brewster <ijbrewster@alaska.edu>) |
| Ответы |
Re: Faster distinct query?
Re: Faster distinct query? |
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: