Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 14DBC9F9928 for ; Sat, 3 Nov 2007 17:22:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 69444-03 for ; Sat, 3 Nov 2007 17:22:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.187]) by postgresql.org (Postfix) with ESMTP id 6A0549F9926 for ; Sat, 3 Nov 2007 17:22:20 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so948305rvb for ; Sat, 03 Nov 2007 13:22:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; bh=uYuqSeEYGtwLHzeFKNHnlno5/cCENq+eAtHu1c6YDdU=; b=DwgvvApDtCyWF/5FL4MBWGj9PDPW8YjUuIiZmi0hoYidU4nQ7ka4JiT6qvRm5RTRrJ2yzGArt0Y9Bs0jPUiQZRqdcTxjt1CwrWeVoJ8Ya+wT2vA+PHDBYotFdsKM6oO/yAt/uWmneoZBAYK1VT7J+HScLwZsaUjicbEuJIOZWlM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=Ts9P5vE1/HEC5CJV1YVhv72LhAFst3c+sze+zIDlO/7DNfodpcHM3UgybM1/wdGmwxNRGL6OARedQoc5+qKDSFeOeiYHF3/0M2LQC+YFQ4uFwAUYZvbQKvg1o6eik++3175HJwCQELFd6SFujiMtmtjyfZdKVh96Z0x4XulTW7I= Received: by 10.142.241.10 with SMTP id o10mr779424wfh.1194121338930; Sat, 03 Nov 2007 13:22:18 -0700 (PDT) Received: by 10.143.35.10 with HTTP; Sat, 3 Nov 2007 13:22:18 -0700 (PDT) Message-ID: Date: Sat, 3 Nov 2007 15:22:18 -0500 From: "Jeff Larsen" To: pgsql-performance@postgresql.org Subject: Union within View vs.Union of Views MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/38 X-Sequence-Number: 27453 Consider: CREATE VIEW_X AS SELECT UNION ALL SELECT UNION ALL SELECT ; versus CREATE VIEW_A AS SELECT ; CREATE VIEW_B AS SELECT ; CREATE VIEW_C AS SELECT ; where , and are each somewhat complex with several joins, but utilizing different tables for each of A, B and C. Performance on SELECT * from VIEW_X WHERE ; was absolutely terrible. But performance on SELECT * from VIEW_A WHERE UNION ALL SELECT * from VIEW_B WHERE UNION ALL SELECT * from VIEW_C WHERE ; was nice and speedy, perhaps 100 times faster than the first. If it's possible to consider this abstractly, is there any particular reason why there is such a vast difference in performance? I would guess that is has something to do with how the WHERE conditions are applied to a view composed of a UNION of queries. Perhaps this is an opportunity for improvement in the code. In the first case, it's as if the server is doing the union on all rows (over 10 million altogether in my case) without filtering, then applying the conditions to the result. Maybe there is no better way. I can post query plans if anyone is interested. I haven't really learned how to make sense out of them myself yet. For my purposes, I'm content to use the union of separate views in my application, so if this doesn't pique anyone's interest, feel free to ignore it. Jeff