Indexing materialized views in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a materialized view in PostgreSQL?

A materialized view is a database object that stores the result of a query physically. It allows faster access to complex query results by saving the data instead of computing it each time.

beginner

Why do we create indexes on materialized views?

Indexes on materialized views improve query performance by allowing faster data retrieval from the stored results, just like indexes on regular tables.

intermediate

How do you create an index on a materialized view in PostgreSQL?

Use the standard CREATE INDEX command on the materialized view name, for example: <br>CREATE INDEX idx_name ON mat_view_name(column_name);

intermediate

Does refreshing a materialized view affect its indexes?

Refreshing a materialized view updates its data but does not drop or recreate its indexes. The indexes remain and continue to speed up queries.

beginner

What is the difference between a materialized view and a regular view regarding indexing?

Regular views do not store data physically and cannot have indexes. Materialized views store data physically and can have indexes to improve query speed.

What command creates an index on a materialized view column in PostgreSQL?

A CREATE MATERIALIZED VIEW INDEX idx ON mat_view(column);

B CREATE INDEX idx ON mat_view(column);

C CREATE VIEW INDEX idx ON mat_view(column);

D CREATE INDEX idx ON VIEW mat_view(column);

The correct syntax is CREATE INDEX on the materialized view name and column.

What happens to indexes when you refresh a materialized view?

A Indexes remain intact and continue to work.

B Materialized views cannot have indexes.

C Indexes are disabled temporarily.

D Indexes are dropped and must be recreated.

Refreshing updates data but does not drop or disable indexes.

Which of the following is true about materialized views?

A They do not store data physically.

B They automatically update when base tables change.

C They can have indexes to speed up queries.

D They cannot be refreshed.

Materialized views store data physically and can have indexes.

Why might you choose to index a materialized view?

A To speed up queries on the stored data.

B To save storage space.

C To slow down query performance.

D To prevent data refresh.

Indexes speed up data retrieval from materialized views.

Can you create a unique index on a materialized view?

A Only if the materialized view is refreshed concurrently.

B No, unique indexes are not allowed.

C Only on regular views, not materialized views.

D Yes, just like on regular tables.

Materialized views support unique indexes similar to tables.

Explain what a materialized view is and why indexing it can improve performance.

Think about how saving data and adding indexes helps avoid repeating complex queries.

Describe the process and syntax to create an index on a materialized view in PostgreSQL.

It is similar to creating an index on a regular table.