From joe@piscitella.com Thu Aug 29 04:46:40 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8772E475B98 for ; Thu, 29 Aug 2002 00:46:40 -0400 (EDT) Received: from ns1.noc.com (cpe-24-221-43-101.az.sprintbbd.net [24.221.43.101]) by postgresql.org (Postfix) with ESMTP id 8A71F475AFB for ; Thu, 29 Aug 2002 00:46:38 -0400 (EDT) Received: from joe (cpe-24-221-43-42.az.sprintbbd.net [24.221.43.42]) by ns1.noc.com (8.11.6/8.11.6) with SMTP id g7T5HUE00464 for ; Wed, 28 Aug 2002 22:17:34 -0700 (MST) (envelope-from joe@piscitella.com) Message-ID: <003d01c24f17$2d21ff00$c801a8c0@joe> From: "JOE" To: Subject: union optimization in views Date: Wed, 28 Aug 2002 21:47:20 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0038_01C24EDC.7C6C28F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/408 X-Sequence-Number: 9254 This is a multi-part message in MIME format. ------=_NextPart_000_0038_01C24EDC.7C6C28F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We are attempting to move a couple of systems from Oracle to Postgres but c= an not do so without application rewrites due to the current use of views w= ith UNIONs and the criticality of the performances of these views. I was wondering if a decision has been made on the optimization with the UN= ION clause in views. There are many documents in the SQL archive showing t= hat the "push down" is not occuring and thus the use of UNION's in views is= limited to case where the data set is small or performance is not a consid= eration. I also looked through the TODO list and didn't see anything (of c= ourse I could have missed references).=20=20 thanks - Joe snip of an Article from SQL archives CREATE VIEW two_tables AS SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id FROM t1 UNION ALL SELECT t2.id, t2.name, NULL, t2.juris_id FROM t2; This works fine as a view, since I have made the id's unique between=20 the two tables (using a sequence). However, as t1 has 100,000 records, = it is vitally important that queries against this view use an index. As it is a Union view, though, they ignore any indexes: > It's probably not pushing the login=3D'asdadad' condition down into the= =20 > queries in the view so it's possibly doing a full union all followed=20 > by the condition (given that it's estimating a larger number of rows=20 > returned). I think there was some question about whether it was safe=20 > to do that optimization (ie, is select * from (a union [all] b) where=20 > condition always the same as > select * from a where condition union [all] > select * from b where condition > ) > > There wasn't any final determination --- it's still an open issue=20 > whether there are any limitations the planner would have to consider=20 > when trying to push down conditions into UNIONs. Offhand it seems to=20 > me that the change is always safe when dealing with UNION ALL, but I'm > not quite convinced about UNION. And what of INTERSECT and EXCEPT? > > Another interesting question is whether there are cases where the=20 > planner could legally push down the condition, but should not because=20 > it would end up with a slower plan. I can't think of any examples=20 > offhand, but that doesn't mean there aren't any. ------=_NextPart_000_0038_01C24EDC.7C6C28F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

We are attempt= ing to move a=20 couple of systems from Oracle to Postgres but can not do so without applica= tion=20 rewrites due to the current use of views with UNIONs and the criticality of= the=20 performances of these views.

I was wondering if a decision has been mad= e on the=20 optimization with the UNION clause in views. There are many documents= in=20 the SQL archive showing that the "push down" is not occuring and thus the u= se of=20 UNION's in views is limited to case where the data set is small or performa= nce=20 is not a consideration. I also looked through the TODO list and didn'= t see=20 anything (of course I could have missed references).

thanks - Joe

snip of an Article from SQL archives

CREATE VIEW two_tables AS
S= ELECT=20 t1.id, t1.name, t1.abbreviation, t1.juris_id
FROM t1
UNIO= N=20 ALL
SELECT t2.id, t2.name, NULL, t2.juris_id
FROM=20 t2;

This works fine as a view, since I have made the id's unique=20 between
the two tables (using a sequence). Howe= ver,=20 as t1 has 100,000 records, it is
vitally important that queries against = this=20 view use an index.
As it is a Union view, though, they ignore any=20 indexes:

> It's probably not pushing the login= =3D'asdadad'=20 condition down into the
> queries in the view so it's possibly doing= a=20 full union all followed
> by the condition (given that it's estimati= ng a=20 larger number of rows
> returned). I think there was some ques= tion=20 about whether it was safe
> to do that optimization (ie, is select *= from=20 (a union [all] b) where
> condition always the same as
> = =20 select * from a where condition union [all]
> select * from b w= here=20 condition
> )
>
> There wasn't any final determination --= -=20 it's still an open issue
> whether there are any limitations the pla= nner=20 would have to consider
> when trying to push down conditions into=20 UNIONs. Offhand it seems to
> me that the change is always saf= e=20 when dealing with UNION ALL, but I'm

> not quite convinced about= =20 UNION. And what of INTERSECT and EXCEPT?
>
> Another=20 interesting question is whether there are cases where the
> planner = could=20 legally push down the condition, but should not because
> it would e= nd up=20 with a slower plan. I can't think of any examples
> offhand, b= ut=20 that doesn't mean there aren't any.

------=_NextPart_000_0038_01C24EDC.7C6C28F0-- From josh@agliodbs.com Thu Aug 29 17:52:02 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E064F475C06 for ; Thu, 29 Aug 2002 13:52:02 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 4C13E4759F2 for ; Thu, 29 Aug 2002 13:52:02 -0400 (EDT) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.5.9) with ESMTP id 1628122; Thu, 29 Aug 2002 10:52:00 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "JOE" , Subject: Re: union optimization in views Date: Thu, 29 Aug 2002 10:52:39 -0700 X-Mailer: KMail [version 1.4] References: <003d01c24f17$2d21ff00$c801a8c0@joe> In-Reply-To: <003d01c24f17$2d21ff00$c801a8c0@joe> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200208291052.39160.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/417 X-Sequence-Number: 9263 Joe, > We are attempting to move a couple of systems from Oracle to Postgres but= =20 can not do so without application rewrites due to the current use of views= =20 with UNIONs and the criticality of the performances of these views. >=20 > I was wondering if a decision has been made on the optimization with the= =20 UNION clause in views. There are many documents in the SQL archive showing= =20 that the "push down" is not occuring and thus the use of UNION's in views i= s=20 limited to case where the data set is small or performance is not a=20 consideration. I also looked through the TODO list and didn't see anything= =20 (of course I could have missed references).=20=20 I'd take this up on PGSQL-HACKERS. The UNION VIEW optimization, last I=20 checked, was stalled mainly because nobody wanted to work on it. Maybe you= =20 can? --=20 -Josh Berkus Aglio Database Solutions San Francisco From sszabo@megazone23.bigpanda.com Tue Sep 3 04:06:05 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3BA94475F0D for ; Tue, 3 Sep 2002 00:06:05 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id C66AA475E32 for ; Tue, 3 Sep 2002 00:06:04 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id F1B2DD602; Mon, 2 Sep 2002 21:06:05 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id E76775C03; Mon, 2 Sep 2002 21:06:05 -0700 (PDT) Date: Mon, 2 Sep 2002 21:06:05 -0700 (PDT) From: Stephan Szabo To: Josh Berkus Cc: JOE , Subject: Re: union optimization in views In-Reply-To: <200208291052.39160.josh@agliodbs.com> Message-ID: <20020902210247.H41635-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/3 X-Sequence-Number: 9280 On Thu, 29 Aug 2002, Josh Berkus wrote: > Joe, > > > We are attempting to move a couple of systems from Oracle to Postgres but > can not do so without application rewrites due to the current use of views > with UNIONs and the criticality of the performances of these views. > > > > I was wondering if a decision has been made on the optimization with the > UNION clause in views. There are many documents in the SQL archive showing > that the "push down" is not occuring and thus the use of UNION's in views is > limited to case where the data set is small or performance is not a > consideration. I also looked through the TODO list and didn't see anything > (of course I could have missed references). > > I'd take this up on PGSQL-HACKERS. The UNION VIEW optimization, last I > checked, was stalled mainly because nobody wanted to work on it. Maybe you > can? Tom sent a patch to -patches against then cvs head that did union/intersect [all] push down last week. Unless someone comes up with a complaint, I'd guess that's going to make it in 7.3