X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id CAF5C9260E6 for ; Sun, 1 Jun 2003 01:41:29 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 35894-08 for ; Sun, 1 Jun 2003 01:41:19 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by developer.postgresql.org (Postfix) with SMTP id BCA5F923489 for ; Sun, 1 Jun 2003 01:41:18 -0400 (EDT) Received: (qmail 26229 invoked by uid 500); 1 Jun 2003 05:43:37 -0000 Date: Sun, 1 Jun 2003 00:43:37 -0500 From: Bruno Wolff III To: Rod Taylor Cc: Brian Tarbox , Postgresql Performance Subject: Re: are views typically any faster/slower than equivilent joins? Message-ID: <20030601054337.GD16259@wolff.to> Mail-Followup-To: Rod Taylor , Brian Tarbox , Postgresql Performance References: <001801c327e9$3c01a5b0$01000001@trouble> <1054440158.11968.86.camel@jester> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1054440158.11968.86.camel@jester> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/3 X-Sequence-Number: 2087 On Sun, Jun 01, 2003 at 00:02:39 -0400, Rod Taylor wrote: > > Some views such as unions will not be as fast as you would like, but > thats a general issue with PostgreSQLs inability to throw away selects > when it won't find results on one side of a union. > > CREATE VIEW sales AS SELECT * FROM sales_archive_2002 UNION ALL SELECT * > FROM sales_current; > > > SELECT * FROM sales WHERE timestamp => CURRENT_TIMESTAMP - INTERVAL '1 > day'; > > The above query would not be so quick. I thought some work had been done on pushing where conditions down into unions? If so the above wouldn't be too bad. It would still look at the archive table, but it should return no rows relatively quickly assuming an appropiate index exists.