How to create an index in postgresql for a view?

Question

PostgreSQL

I execute :
create index idx_denormalized_account_user_products_account_id
on public.denormalized_account_user_products (account_id);
Throws an error:
[42809] ERROR: "denormalized_account_user_products" is not a table or materialized view

Answer 1

No way.
view does not store any data, and since there is no data, there is nothing to build an index on.
An example of the fundamental problem of an index on a view: if you make a view of the form select foo, avg (bar) from tablename group by foo; - how to recalculate data when changing rows in tablename?
An index can be created on a materialized view - because such a view stores data directly. But you need to update this data manually with a REFRESH MATERIALIZED VIEW query, which will execute the query, write its result to a new heap, then replace the old heap with a new one (if concurrently is not specified) or update mismatched rows (for concurrently)