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 3032752824 for ; Thu, 26 May 2005 13:53:58 -0300 (ADT) 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 77858-04 for ; Thu, 26 May 2005 16:53:55 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 558E152802 for ; Thu, 26 May 2005 13:53:54 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4QGrsCm003340; Thu, 26 May 2005 12:53:54 -0400 (EDT) To: Sam Mason Cc: pgsql-performance@postgresql.org Subject: Re: Optimising queries involving unions In-reply-to: <20050526152203.GI15205@colo.samason.me.uk> References: <20050526152203.GI15205@colo.samason.me.uk> Comments: In-reply-to Sam Mason message dated "Thu, 26 May 2005 16:22:03 +0100" Date: Thu, 26 May 2005 12:53:54 -0400 Message-ID: <3339.1117126434@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/396 X-Sequence-Number: 12560 Sam Mason writes: > Here's a bad example: > SELECT u.txt > FROM smalltable t, ( > SELECT id, txt FROM largetable1 > UNION ALL > SELECT id, txt FROM largetable2) u > WHERE t.id = u.id > AND t.foo = 'bar'; > I was hoping that "smalltable" would get moved up into the union, > but it doesn't at the moment and the database does a LOT of extra > work. I'm afraid we're a long way away from being able to do that; the parse/plan representation of UNION wasn't chosen with an eye to being able to optimize it at all :-(. We can push restriction clauses down into a union, but we can't do much with join clauses, because they necessarily refer to tables that don't even exist within the sub-query formed by the UNION. It'd be nice to fix this someday, but don't hold your breath ... regards, tom lane