1_datamodel.txt

+-----------+
| customers |---------------------------------+
+-----------+                                 |
      |                                       |
      |                                       |
      v                                       v
+-----------------------------+   +------------------------------+
| customers_billing_addresses |   | customers_shipping_addresses |
+-----------------------------+   | ----                         |
             |                    | primary: boolean             |
             |                    +------------------------------+ 
             v                                   |
        +---------+                              |
        |addresses|<-----------------------------+
        +---------+
            |
            |
            |
            v
        +------+
        |states|
        +------+

Supposing we want to create a view of this data based on the query below.  I want to say 
that the materialized view has significant advantages over the regular view, in 
terms of performance.  The increase in perf based on the explains doesn't look 
huge, so what to make of this?

2_query.sql

SELECT
  customers.id AS customer_id,
  customers.first_name,
  customers.last_name,
  customers.email,
  customers.username,
  customers.created_at     AS joined_at,
  billing_address.street   AS billing_street,
  billing_address.city     AS billing_city,
  billing_state.code       AS billing_state,
  billing_address.zipcode  AS billing_zipcode,
  shipping_address.street  AS shipping_street,
  shipping_address.city    AS shipping_city,
  shipping_state.code      AS shipping_state,
  shipping_address.zipcode AS shipping_zipcode
FROM
  customers
LEFT JOIN customers_billing_addresses ON 
  customers.id = customers_billing_addresses.customer_id
LEFT JOIN addresses billing_address ON
  billing_address.id = customers_billing_addresses.address_id
LEFT JOIN states billing_state ON
  billing_address.state_id = billing_state.id
LEFT JOIN customers_shipping_addresses ON 
  customers.id = customers_shipping_addresses.customer_id
LEFT JOIN addresses shipping_address ON
  shipping_address.id = customers_shipping_addresses.address_id AND
  customers_shipping_addresses.primary = true
LEFT JOIN states shipping_state ON
  shipping_address.state_id = shipping_state.id

3_explain_on_view.txt

explain analyze select * from customers_view where customer_id = 123123;
                                                                                             QUERY PLAN                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=2.40..63.40 rows=3 width=224) (actual time=2.574..2.592 rows=3 loops=1)
   ->  Nested Loop Left Join  (cost=2.26..62.89 rows=3 width=225) (actual time=2.568..2.583 rows=3 loops=1)
         Join Filter: customers_shipping_addresses."primary"
         Rows Removed by Join Filter: 2
         ->  Nested Loop Left Join  (cost=1.84..37.53 rows=3 width=187) (actual time=2.551..2.558 rows=3 loops=1)
               Join Filter: (customers.id = customers_shipping_addresses.customer_id)
               ->  Nested Loop Left Join  (cost=1.41..25.51 rows=1 width=182) (actual time=1.886..1.891 rows=1 loops=1)
                     ->  Nested Loop Left Join  (cost=1.27..25.34 rows=1 width=183) (actual time=1.874..1.877 rows=1 loops=1)
                           ->  Nested Loop Left Join  (cost=0.84..16.89 rows=1 width=144) (actual time=1.208..1.210 rows=1 loops=1)
                                 Join Filter: (customers.id = customers_billing_addresses.customer_id)
                                 ->  Index Scan using customers_pkey on customers  (cost=0.42..8.44 rows=1 width=140) (actual time=0.515..0.516 rows=1 loops=1)
                                       Index Cond: (id = 123123)
                                 ->  Index Scan using customer_has_one_billing_address on customers_billing_addresses  (cost=0.42..8.44 rows=1 width=8) (actual time=0.683..0.684 rows=1 loops=1)
                                       Index Cond: (customer_id = 123123)
                           ->  Index Scan using addresses_pkey on addresses billing_address  (cost=0.43..8.45 rows=1 width=47) (actual time=0.660..0.660 rows=1 loops=1)
                                 Index Cond: (id = customers_billing_addresses.address_id)
                     ->  Index Scan using states_pkey on states billing_state  (cost=0.14..0.16 rows=1 width=7) (actual time=0.007..0.007 rows=1 loops=1)
                           Index Cond: (billing_address.state_id = id)
               ->  Index Scan using customer_does_not_have_duplicate_shipping_addresses on customers_shipping_addresses  (cost=0.42..11.98 rows=3 width=9) (actual time=0.657..0.658 rows=3 loops=1)
                     Index Cond: (customer_id = 123123)
         ->  Index Scan using addresses_pkey on addresses shipping_address  (cost=0.43..8.45 rows=1 width=47) (actual time=0.005..0.006 rows=1 loops=3)
               Index Cond: (id = customers_shipping_addresses.address_id)
   ->  Index Scan using states_pkey on states shipping_state  (cost=0.14..0.16 rows=1 width=7) (actual time=0.002..0.002 rows=0 loops=3)
         Index Cond: (shipping_address.state_id = id)
 Planning time: 0.832 ms
 Execution time: 2.696 ms
(26 rows)

4_explain_on_materialized_view.txt

explain analyze select * from customers_materialized_view where customer_id = 123123;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customers_materialized_view  (cost=94.04..11602.51 rows=4854 width=396) (actual time=0.946..1.005 rows=3 loops=1)
   Recheck Cond: (customer_id = 123123)
   Heap Blocks: exact=3
   ->  Bitmap Index Scan on customers_materialized_view_id  (cost=0.00..92.83 rows=4854 width=0) (actual time=0.909..0.909 rows=3 loops=1)
         Index Cond: (customer_id = 123123)
 Planning time: 0.313 ms
 Execution time: 1.034 ms
(7 rows)

5_table_defs.txt

davec@[local][]# \d customers
                                     Table "public.customers"
   Column   |            Type             |                       Modifiers                        
------------+-----------------------------+--------------------------------------------------------
 id         | integer                     | not null default nextval('customers_id_seq'::regclass)
 first_name | character varying           | not null
 last_name  | character varying           | not null
 email      | character varying           | not null
 username   | character varying           | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "index_customers_on_email" UNIQUE, btree (email)
    "index_customers_on_username" UNIQUE, btree (username)
    "customers_lower_email" btree (lower(email::text))
    "customers_lower_first_name" btree (lower(first_name::text) varchar_pattern_ops)
    "customers_lower_last_name" btree (lower(last_name::text) varchar_pattern_ops)

davec@[local][]# \d customers_billing_addresses
                            Table "public.customers_billing_addresses"
   Column    |  Type   |                                Modifiers                                 
-------------+---------+--------------------------------------------------------------------------
 id          | integer | not null default nextval('customers_billing_addresses_id_seq'::regclass)
 customer_id | integer | not null
 address_id  | integer | not null
Indexes:
    "customers_billing_addresses_pkey" PRIMARY KEY, btree (id)
    "customer_has_one_billing_address" UNIQUE, btree (customer_id)

davec@[local][]# \d customers_shipping_addresses
                            Table "public.customers_shipping_addresses"
   Column    |  Type   |                                 Modifiers                                 
-------------+---------+---------------------------------------------------------------------------
 id          | integer | not null default nextval('customers_shipping_addresses_id_seq'::regclass)
 customer_id | integer | not null
 address_id  | integer | not null
 primary     | boolean | not null default false
Indexes:
    "customers_shipping_addresses_pkey" PRIMARY KEY, btree (id)
    "customer_does_not_have_duplicate_shipping_addresses" UNIQUE, btree (customer_id, address_id)

davec@[local][]# \d addresses
                               Table "public.addresses"
  Column  |       Type        |                       Modifiers                        
----------+-------------------+--------------------------------------------------------
 id       | integer           | not null default nextval('addresses_id_seq'::regclass)
 street   | character varying | not null
 city     | character varying | not null
 state_id | integer           | not null
 zipcode  | character varying | not null
Indexes:
    "addresses_pkey" PRIMARY KEY, btree (id)

davec@[local][]# \d states
                              Table "public.states"
 Column |       Type        |                      Modifiers                      
--------+-------------------+-----------------------------------------------------
 id     | integer           | not null default nextval('states_id_seq'::regclass)
 code   | character varying | not null
 name   | character varying | not null
Indexes:
    "states_pkey" PRIMARY KEY, btree (id)