Re: Unions and where optimisation - Mailing list pgsql-performance

From Hannu Krosing
Subject Re: Unions and where optimisation
Date
Msg-id 1042041735.3237.1.camel@huli
Whole thread Raw
In response to Unions and where optimisation  (Boris Klug <boris.klug@control.de>)
List pgsql-performance
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>

pgsql-performance by date:

Previous
From: Achilleus Mantzios
Date:
Subject: Re: [SQL] 7.3.1 index use / performance
Next
From: Tomasz Myrta
Date:
Subject: Re: Unions and where optimisation