X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 64CB73A469D for ; Mon, 1 Nov 2004 21:40:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77808-10 for ; Mon, 1 Nov 2004 21:40:33 +0000 (GMT) Received: from mail.atua.com.br (unknown [200.248.138.61]) by svr1.postgresql.org (Postfix) with ESMTP id 490D63A4696 for ; Mon, 1 Nov 2004 21:40:31 +0000 (GMT) Received: from [10.0.0.171] (ns2.atua.com.br [200.248.138.60]) by mail.atua.com.br (Postfix) with ESMTP id 1F2141EC324 for ; Mon, 1 Nov 2004 19:40:31 -0200 (BRST) Subject: Performance difference when using views From: Alvaro Nunes Melo To: Pgsql-Performance Content-Type: text/plain Message-Id: <1099345230.8204.30.camel@localhost> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 01 Nov 2004 19:40:30 -0200 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/8 X-Sequence-Number: 8962 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 | +---------------------------------------------------+