Enterprise Rails

Results

The results of using a materialized view rather than an unmaterialized one are quite impressive. In Example 12-20, a select from both for current, non-sold-out showtimes is analyzed.

First, the count of records in each table is selected to give you an idea of how much data we are dealing with. In fact, it is not too much data compared to what a real production site selling tickets for all theatres and all movies nationally might have in its database. Our data set likely accounts for a day or at most a week’s worth of accumulated data on a real system.

Next, we select all of the current, non-sold-out showtimes from the unmaterialized view. On a Dual Core 2.1 Ghz MacBook Pro, the query takes 1.16 seconds. Next we issue the same select against the materialized view. It takes 0.013 seconds. With this dataset, selecting from the materialized view is almost 100 times faster. As the dataset grows, the time required to select from the unmaterialized view increases, while the time to request from the materialized view remains nearly constant.

Example 12-20. Comparison of runtimes on a view versus a materialized view

movies_development=# select (select count(*) from movies) as movies, (select count(*) from theatres) as theatres, (select count(*) from movie_showtimes) as showtimes, (select count(*) from orders) as orders, (select count(*) from purchased_tickets) as tickets; movies | theatres | showtimes | orders | tickets --------+----------+-----------+--------+--------- 44 | 6 | 20201 ...