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?  (Israel Brewster <ijbrewster@alaska.edu>)
Re: Faster distinct query?  (Mladen Gogala <gogala.mladen@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Israel Brewster
Дата:
Сообщение: Faster distinct query?
Следующее
От: Michael Lewis
Дата:
Сообщение: Re: Faster distinct query?