Enterprise Rails

Cache Indexes

Our materialized view implementation is actually rather useless if we do not add indexes. Although we don’t need to evaluate the complex view query when we query the materialized view, without indexes, each request would need to run a full table scan to return any results. Indexing makes queries by id or by one of our original filters close to instantaneous.

There is a minimal set of indexes we need on a materialized view. First, we need to index the primary key column. It’s fine to do this by creating an explicit primary key. Next, we need to add indexes on the dirty and expiry columns, since they are part of the where clause of the reconciler view. Indexing these columns keeps that part of our implementation fast. Finally, we should index the filters that we recast as columns, current and sold_out, since it’s likely we’ll be filtering on these columns frequently. Apart from this set—the primary key, the invalidation implementation columns, and the filter columns—you can index any columns in your materialized view that your application will select or filter on. The creation of our primary key and indexes is shown in Example 12-19.

Example 12-19. A minimal set of indices on a materialized view

alter table movie_showtimes_with_current_and_sold_out_and_dirty_and_expiry
 add primary key (id);

create index movie_showtimes_with_current_and_sold_out_dirty_expiry_idx
  on movie_showtimes_with_current_and_sold_out_and_dirty_and_expiry(dirty, expiry); create index movie_showtimes_with_current_and_sold_out_current_idx ...