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 723883A4682 for ; Mon, 1 Nov 2004 22:31:13 +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 94707-07 for ; Mon, 1 Nov 2004 22:31:09 +0000 (GMT) Received: from cmailg4.svr.pol.co.uk (cmailg4.svr.pol.co.uk [195.92.195.174]) by svr1.postgresql.org (Postfix) with ESMTP id CB5EE3A4680 for ; Mon, 1 Nov 2004 22:31:11 +0000 (GMT) Received: from modem-465.orangutan.dialup.pol.co.uk ([217.135.225.209] helo=[192.168.0.102]) by cmailg4.svr.pol.co.uk with esmtp (Exim 4.41) id 1COkhh-0002vZ-Sm; Mon, 01 Nov 2004 22:31:10 +0000 Subject: Re: Performance difference when using views From: Simon Riggs To: Alvaro Nunes Melo Cc: Pgsql-Performance In-Reply-To: <1099345230.8204.30.camel@localhost> References: <1099345230.8204.30.camel@localhost> Content-Type: text/plain Organization: 2nd Quadrant Message-Id: <1099348130.2709.105.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 01 Nov 2004 22:28:50 +0000 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/11 X-Sequence-Number: 8965 On Mon, 2004-11-01 at 21:40, Alvaro Nunes Melo wrote: > 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; > Try this.... SELECT p.person_id, ci.city_id, ci.city_name, s.state_id, s.state_acronym FROM person p LEFT OUTER JOIN ( 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 u ON ci.state_id = s.state_id ) USING (person_id) WHERE a.adress_type = 2 AND p.person_id = 19257; Which should return the same answer, and also hopefully the same plan. -- Best Regards, Simon Riggs