Re: Unions and where optimisation

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 <hannu@tm.ee>