X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 75470DA40E for ; Thu, 6 Oct 2005 14:23:02 -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 15631-04 for ; Thu, 6 Oct 2005 17:22:58 +0000 (GMT) Received: from mail.aveo.aveopharma.com (67.109.105.227.ptr.us.xo.net [67.109.105.227]) by svr1.postgresql.org (Postfix) with SMTP id 8F73CDA40C for ; Thu, 6 Oct 2005 14:22:57 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: UNION index use help Date: Thu, 6 Oct 2005 13:25:50 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] UNION index use help Thread-Index: AcXKk+sWtBB5cYmsRRqlcdFmRX6YTAABvf2g From: "Dmitri Bichko" To: "Tom Lane" Cc: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.066 required=5 tests=[AWL=0.016, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/58 X-Sequence-Number: 22911 That's exactly it - thanks, works perfectly now! For the record, it's 8.0.3 Dmitri > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 > Sent: Thursday, October 06, 2005 12:32 PM > To: Dmitri Bichko > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] UNION index use help=20 >=20 >=20 > "Dmitri Bichko" writes: > > Ok, I'm thoroughly confused. >=20 > You didn't say which PG version you are using, but if it's=20 > something reasonably recent then it should be able to push=20 > upper qual conditions down into a UNION. I think you are=20 > getting bit by this restriction: >=20 > * 3. For subqueries using UNION/UNION=20 > ALL/INTERSECT/INTERSECT ALL, we can > * push quals into each component query, but the quals can=20 > only reference > * subquery columns that suffer no type coercions in the set=20 > operation. > * Otherwise there are possible semantic gotchas. >=20 > You're getting burnt because you're unioning a text with a=20 > varchar. Make the column types the same and it'll work better. >=20 > regards, tom lane >=20 The information transmitted is intended only for the person or entity to = which it is addressed and may contain confidential and/or privileged mate= rial. Any review, retransmission, dissemination or other use of, or takin= g of any action in reliance upon, this information by persons or entities= other than the intended recipient is prohibited. If you received this in= error, please contact the sender and delete the material from any comput= er