![]() The process is the same as for any other table: We can simply query the data.Īt this point, the only suboptimal thing is the query optimizer’s estimate (= 1130 rows). ![]() Looking at the execution plan ( explain) shows that there is no difference between a normal table and a materialized view. If you want to query the materialized view, you can do it just like you would query a normal PostgreSQL table: Public | t_demo | table | hs | permanent | 423 MB |Ĥ23 MB vs. Public | mat_view | materialized view | hs | permanent | 16 kB | Schema | Name | Type | Owner | Persistence | Size | Description ![]() What is really important to note here is the size of the materialized view compared to the underlying table: Here is an example of a materialized view:ĭemo=# CREATE MATERIALIZED VIEW mat_view AS ) ]īasically, a materialized view has a name, some parameters, and is based on a query. To create a materialized view in PostgreSQL, we can make use of the following syntax specification:ĭescription: define a new materialized viewĬREATE MATERIALIZED VIEW table_name We have created 10 million rows organized in 2 groups. Creating a materialized viewīefore we can actually take a look at materialized views and figure out how they work, we have to import some sample data which we can use as the basis for our calculations:ĭemo=# CREATE TABLE t_demo (grp int, data numeric) ĭemo=# INSERT INTO t_demo SELECT 1, random()ĭemo=# INSERT INTO t_demo SELECT 2, random() The main questions are now: What are the pitfalls, and how can you make use of materialized views in the first place? Let’s dive in and find out. Naturally, PostgreSQL also provides support for materialized views, and offers the end-user a powerful tool to handle more time-consuming requests. Materialized views are an integral feature of pretty much all advanced database systems. A “materialized view” is a database object which stores the result of a precalculated database query and makes it easy to refresh this result as needed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |