Как создать индекс в postgresql для view?

Question

[postgresdev]

Выполняю:
create index idx_denormalized_account_user_products_account_id
on public.denormalized_account_user_products (account_id);

Выдает ошибку:
[42809] ERROR: "denormalized_account_user_products" is not a table or materialized view

Answer 1

[Melkij]

Никак.
view не хранит никаких данных, а раз нет данных - то не по чему строить индекс.
Пример принципиальной проблемы индекса на view: если сделать view вида select foo, avg(bar) from tablename group by foo; - как пересчитывать данные при изменении строк в tablename?

Индекс может быть создан на материализованном представлении (materialized view) - потому что такое представление данные хранит непосредственно. Но обновлять эти данные требуется вручную запросом REFRESH MATERIALIZED VIEW, который выполнит запрос, запишет его результат в новый heap, затем заменит старый heap новым (если не указан concurrently) либо обновит несовпадающие строки (для concurrently)

Comments

[postgresdev]

при обращении к view(просмотр данных) они там есть. Соотвественно это materialized view?

[postgresdev]

Запрос на создание view:

create view denormalized_account_user_products(id, user_id, account_id, products) as
SELECT aup.id,
aup.user_id,
aup.account_id,
pal.products
FROM (account_user_products aup
JOIN product_access_lists pal ON ((pal.id = aup.product_access_list_id)));

alter table denormalized_account_user_products
owner to officeteam;

[Melkij]

Ещё раз: в view данных нет. View не хранит ни байта пользовательских данных.
Когда вы обращаетесь к view - синтаксическое дерево запроса в view подставляется вместо имени view, затем этот большой запрос оптимизируется как будто вы view вообще не использовали, а написали этот большой запрос сами. Поэтому в view всегда актуальный снимок MVCC видимости, но индексы навесить невозможно.

Данные хранит CREATE MATERIALIZED VIEW. Данные ранит, поэтому добавить поддержку индексов было возможно. Но данные в нём на момент создания либо последнего refresh.

[postgresdev]

Melkij, ок, спасибо. к этому моменту уже нашел ответ.