Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B1D334763CD for ; Wed, 8 Jan 2003 09:08:37 -0500 (EST) Received: from localhost.localdomain (sein.itera.ee [194.126.109.126]) by postgresql.org (Postfix) with ESMTP id A3B254762AA for ; Wed, 8 Jan 2003 09:08:36 -0500 (EST) Received: (from hannu@localhost) by localhost.localdomain (8.11.6/8.11.6) id h08G2G103290; Wed, 8 Jan 2003 16:02:16 GMT X-Authentication-Warning: localhost.localdomain: hannu set sender to hannu@tm.ee using -f Subject: Re: Unions and where optimisation From: Hannu Krosing To: Boris Klug Cc: "pgsql-performance@postgresql.org" In-Reply-To: <200301081425.48597.boris.klug@control.de> References: <200301081425.48597.boris.klug@control.de> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: Message-Id: <1042041735.3237.1.camel@huli> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 08 Jan 2003 16:02:15 +0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/54 X-Sequence-Number: 701 On Wed, 2003-01-08 at 13:25, Boris Klug wrote: > Hello! > > I am quite new in the PostgreSQL performance business, done a few years Oracle > stuff before. My ist question is the following: > > We have three table, lets name them rk150, 151 and rk152. They all have a > timestamp and a order number in common but than different data after this. > Now I need the data from all tables in one view for a given order number, so > I created a view > > create view orderevents as > select ts, aufnr from rk150 > union > select ts, aufnr from rk151 > union > select ts, aufnr from rk152; try making the orderevents view like this: create view orderevents as select rk.aufnr, sub.ts from rk150 rk, ( select ts from rk150 where aufnr = rk.aufr union select ts from rk151 where aufnr = rk.aufr union select ts from rk152 where aufnr = rk.aufr ) as sub ; this could/should force your desired behavior. > My question now: Is the optimizer able to move the where clause into unions? > If so, how I can get him to do it? > > Thank you for the help in advance! -- Hannu Krosing