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--