May 2018
Intermediate to advanced
576 pages
30h 25m
English
Let's create the following view:
CREATE VIEW v_dish ASSELECT dish_description, count(*)FROM dish JOIN eater USING (dish_id)GROUP BY dish_descriptionORDER BY 1;
Then, we'll query it:
SELECT * FROM v_dish;
We obtain the following output:
dish_description | count ------------------+-------- Lentils | 64236 Mango | 66512 Plantain | 74058 Rice | 90222 Tea | 204972(5 rows)
With a very similar syntax, we create a materialized view with the same underlying query:
CREATE MATERIALIZED VIEW m_dish ASSELECT dish_description, count(*)FROM dish JOIN eater USING (dish_id)GROUP BY dish_descriptionORDER BY 1;
The corresponding query yields the same output as before:
SELECT * FROM m_dish;
The materialized version is much faster than the non-materialized ...