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 353978B9BF5 for ; Thu, 3 Feb 2005 06:42:33 +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 92775-07 for ; Thu, 3 Feb 2005 06:42:31 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id B23798B9B9A for ; Thu, 3 Feb 2005 06:42:30 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6983725; Wed, 02 Feb 2005 22:44:13 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: Klint Gore Subject: Re: horizontal partition Date: Wed, 2 Feb 2005 22:41:57 -0800 User-Agent: KMail/1.6.2 Cc: Gaetano Mendola , pgsql-performance@postgresql.org References: <200502021209.06878.josh@agliodbs.com> <42019B1735F.1271KG@129.180.47.120> In-Reply-To: <42019B1735F.1271KG@129.180.47.120> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200502022241.57799.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/51 X-Sequence-Number: 10380 Klint, > This is how I interpret it (if anyone wants to set me straight or > improve on it feel free) > > Views are implemented as rules. > > Rules are pretty much just a macro to the query builder. When it sees > the view, it replaces it with the implementation of the view. Right so far. > > When you join a view to a table, it generates a subselect of the > implementation and joins that to the other table. More or less. A join set and a subselect are not really different in the planner. > So the subselect will generate the entire set of data from the view > before it can use the join to eliminate rows. Well, not exactly. That's what's happening in THIS query, but it doesn't happen in most queries, no matter how many view levels you nest (well, up to the number FROM_COLLAPSE_LIMIT, anyway). The issue here is that the planner is capable of "pushing down" the WHERE criteria into the first view, but not into the second, "nested" view, and so postgres materializes the UNIONed data set before perfoming the join. Thing is, I seem to recall that this particular issue was something Tom fixed a while ago. Which is why I wanted to know what version Gaetano is using. -- Josh Berkus Aglio Database Solutions San Francisco