Materialized Views in PostgreSQL

Kyle Waters

Venturedata, LLC

Why a materialized view?

Materials

pgm_demo.tar.bz2 is a directory of sql files that will be used in this presentation

Our Tables

Methods

Page Views

Our Report

SELECT page,view_time::DATE,method,COUNT(*),AVG(load_time) FROM page_views LEFT JOIN methods ON (page_views.method_index=methods.method_index) GROUP BY page,view_time::DATE,method,methods.method_index ORDER BY view_time::DATE,page,methods.method_index

Views in Postgresql

Materialized Views in Postgresql

Simple Eager View

Optimized Eager View

Questions? Comments?