Performance difference when using views - Mailing list pgsql-performance

Hi,

I have some views that are used to make some queries simplest. But when
I use them there is a performance loss, because the query don't use
indexes anymore. Below I'm sending the query with and without the view,
its execution times, explains and the view's body. I didn't understood
the why the performance is so different (20x in seconds, 1000x in page
reads) if the queries are semantically identical.

Shouldn't I use views in situations like this? Is there some way to use
the view and the indexes?

--------------
-- View body
--------------

CREATE VIEW vw_test AS
SELECT e.person_id, ci.city_id, ci.city_name, s.state_id,
s.state_acronym
  FROM address a
       LEFT OUTER JOIN zip zp ON a.zip_code_id = zp.zip_code_id
       LEFT OUTER JOIN city ci ON ci.city_id = zp.city_id
       LEFT OUTER JOIN state s ON ci.state_id = s.state_id
 WHERE a.adress_type = 2;

---------------------
-- Without the view
---------------------

SELECT p.person_id, ci.city_id, ci.city_name, s.state_id,
s.state_acronym
  FROM person p
     LEFT OUTER JOIN address e USING (person_id)
       LEFT OUTER JOIN zip zp ON a.zip_code_id = zp.zip_code_id
       LEFT OUTER JOIN city ci ON ci.city_id = zp.city_id
       LEFT OUTER JOIN state u ON ci.state_id = s.state_id
 WHERE a.adress_type = 2
 AND p.person_id = 19257;

 person_id | city_id   | city_name | state_id | state_acronym
-----------+-----------+-----------+----------+---------------
     19257 |     70211 | JAGUARAO  |       22 | RS
(1 record)
Time: 110,047 ms

QUERY PLAN
---------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..20.04 rows=1 width=33)
   Join Filter: ("outer".state_id = "inner".state_id)
   ->  Nested Loop Left Join  (cost=0.00..18.43 rows=1 width=27)
         ->  Nested Loop Left Join  (cost=0.00..13.87 rows=1 width=8)
               ->  Nested Loop  (cost=0.00..10.75 rows=1 width=8)
                     ->  Index Scan using pk_person on person p
(cost=0.00..5.41 rows=1 width=4)
                           Index Cond: (person_id = 19257)
                     ->  Index Scan using un_address_adress_type on
address e  (cost=0.00..5.33 rows=1 width=8)
                           Index Cond: (19257 = person_id)
                           Filter: (adress_type = 2)
               ->  Index Scan using pk_zip on zip zp  (cost=0.00..3.11
rows=1 width=8)
                     Index Cond: ("outer".zip_code_id = zp.zip_code_id)
         ->  Index Scan using pk_city on city ci  (cost=0.00..4.55
rows=1 width=23)
               Index Cond: (ci.city_id = "outer".city_id)
   ->  Seq Scan on state u  (cost=0.00..1.27 rows=27 width=10)
(15 records)

---------------------
-- With the view
---------------------

SELECT p.person_id, t.city_id, t.city_name, t.state_id, t.state_acronym
  FROM person p
     LEFT OUTER JOIN vw_test t USING (person_id)
 WHERE p.person_id = 19257;

 person_id | city_id   | city_name | state_id | state_acronym
-----------+-----------+-----------+----------+--------------
     19257 |     70211 | JAGUARAO  |       22 | RS
(1 record)
Time: 1982,743 ms

QUERY PLAN
---------------------------------------------------------------------
 Nested Loop Left Join  (cost=10921.71..28015.63 rows=1 width=33)
   Join Filter: ("outer".person_id = "inner".person_id)
   ->  Index Scan using pk_person on person p  (cost=0.00..5.41 rows=1
width=4)
         Index Cond: (person_id = 19257)
   ->  Hash Left Join  (cost=10921.71..27799.55 rows=16854 width=33)
         Hash Cond: ("outer".state_id = "inner".state_id)
         ->  Hash Left Join  (cost=10920.38..27545.40 rows=16854
width=27)
               Hash Cond: ("outer".city_id = "inner".city_id)
               ->  Hash Left Join  (cost=10674.20..26688.88 rows=16854
width=8)
                     Hash Cond: ("outer".zip_code_id =
"inner".zip_code_id)
                     ->  Seq Scan on address e  (cost=0.00..1268.67
rows=16854 width=8)
                           Filter: (adress_type = 2)
                     ->  Hash  (cost=8188.36..8188.36 rows=387936
width=8)
                           ->  Seq Scan on zip zp  (cost=0.00..8188.36
rows=387936 width=8)
               ->  Hash  (cost=164.94..164.94 rows=9694 width=23)
                     ->  Seq Scan on city ci  (cost=0.00..164.94
rows=9694 width=23)
         ->  Hash  (cost=1.27..1.27 rows=27 width=10)
               ->  Seq Scan on state u  (cost=0.00..1.27 rows=27
width=10)
(18 records)

Best regards,

--
+---------------------------------------------------+
|  Alvaro Nunes Melo    Atua Sistemas de Informacao |
| al_nunes@atua.com.br        www.atua.com.br       |
|    UIN - 42722678            (54) 327-1044        |
+---------------------------------------------------+