pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Unions and where optimisation
8+ messages / 4 participants
[nested] [flat]

* Unions and where optimisation
@ 2003-01-08 13:25 Boris Klug <boris.klug@control.de>
  2003-01-08 14:40 ` Re: Unions and where optimisation Tomasz Myrta <jasiek@klaster.net>
  2003-01-08 16:02 ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
  0 siblings, 2 replies; 8+ messages in thread

From: Boris Klug @ 2003-01-08 13:25 UTC (permalink / raw)
  To: pgsql-performance

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;

When I does a "select * from orderevents where aufnr='1234'" it takes over 14 
seconds!
The problem is now that PostgreSQL first does the union with all the three 
tables and after this sorts out the right rows:

Subquery Scan a  (cost=54699.06..56622.18 rows=38462 width=20)
  ->  Unique  (cost=54699.06..56622.18 rows=38462 width=20)
        ->  Sort  (cost=54699.06..54699.06 rows=384624 width=20)
              ->  Append  (cost=0.00..10689.24 rows=384624 width=20)
                    ->  Subquery Scan *SELECT* 1
                           (cost=0.00..8862.52 rows=314852 width=20)
                          ->  Seq Scan on rk150 
                           (cost=0.00..8862.52 rows=314852 width=20)
                    ->  Subquery Scan *SELECT* 2  
                           (cost=0.00..1208.58 rows=45858 width=20)
                          ->  Seq Scan on rk151  
                           (cost=0.00..1208.58 rows=45858 width=20)
                    ->  Subquery Scan *SELECT* 3 
                           (cost=0.00..618.14 rows=23914 width=20)
                          ->  Seq Scan on rk152  
                           (cost=0.00..618.14 rows=23914 width=20)

A better thing would it (Oracle does this and I think I have seen it on 
PostgreSQL before), that the where-clause is moved inside every select so we 
have something like this (written by hand):

select * from (
   select zeit, aufnr from rk150 where aufnr='13153811'
     union
   select zeit, aufnr from rk151 where aufnr='13153811'
     union
   select zeit, aufnr from rk152 where aufnr='13153811')
 as A;

This takes less than 1 second because the nr of rows that have to be joined 
are only 45 (optimizer expects 4), not > 300.000:

Subquery Scan a  (cost=45.97..46.19 rows=4 width=20)
  ->  Unique  (cost=45.97..46.19 rows=4 width=20)
        ->  Sort  (cost=45.97..45.97 rows=45 width=20)
              ->  Append  (cost=0.00..44.74 rows=45 width=20)
                    ->  Subquery Scan *SELECT* 1 
                           (cost=0.00..32.22 rows=31 width=20)
                          ->  Index Scan using rk150_uidx_aufnr on rk150  
                           (cost=0.00..32.22 rows=31 width=20)
                    ->  Subquery Scan *SELECT* 2 
                           (cost=0.00..7.67 rows=9 width=20)
                          ->  Index Scan using rk151_uidx_aufnr on rk151  
                           (cost=0.00..7.67 rows=9 width=20)
                    ->  Subquery Scan *SELECT* 3  
                           (cost=0.00..4.85 rows=5 width=20)
                          ->  Index Scan using rk152_uidx_aufnr on rk152 
                           (cost=0.00..4.85 rows=5 width=20)

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!

-- 
Dipl. Inform. Boris Klug, control IT GmbH, Germany



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
@ 2003-01-08 14:40 ` Tomasz Myrta <jasiek@klaster.net>
  1 sibling, 0 replies; 8+ messages in thread

From: Tomasz Myrta @ 2003-01-08 14:40 UTC (permalink / raw)
  To: Boris Klug <boris.klug@control.de>; +Cc: pgsql-performance

Boris Klug wrote:

> create view orderevents as
>    select ts, aufnr from rk150
>      union
>    select ts, aufnr from rk151
>      union
>    select ts, aufnr from rk152;

I lost some time and I didn't find valid solution for this kind of query :-(

I solved it (nice to hear about better solution) using table inheritance.

create table rk_master(
fields...
fields...
);

create table rk150 () inherits rk_master;
create table rk151 () inherits rk_master;
create table rk152 () inherits rk_master;

now you can just create simple view:
select ts, aufnr from rk_master;

Regards,
Tomasz Myrta




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
@ 2003-01-08 16:02 ` Hannu Krosing <hannu@tm.ee>
  2003-01-08 14:32   ` Re: Unions and where optimisation Tomasz Myrta <jasiek@klaster.net>
  1 sibling, 1 reply; 8+ messages in thread

From: Hannu Krosing @ 2003-01-08 16:02 UTC (permalink / raw)
  To: Boris Klug <boris.klug@control.de>; +Cc: 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>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 16:02 ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
@ 2003-01-08 14:32   ` Tomasz Myrta <jasiek@klaster.net>
  2003-01-08 14:36     ` Re: Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 16:49     ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
  0 siblings, 2 replies; 8+ messages in thread

From: Tomasz Myrta @ 2003-01-08 14:32 UTC (permalink / raw)
  To: Hannu Krosing <hannu@tm.ee>; +Cc: Boris Klug <boris.klug@control.de>; pgsql-performance

Hannu Krosing wrote:

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

Hannu, does it work?
Few months ago I lost some time trying to create this kind of query and 
I always got error, that subselect doesn't knows anything about upper 
(outer?) table.

In this query you should get error:
"relation rk does not exist".

What version of postgres do you have?
Tomasz Myrta




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 16:02 ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
  2003-01-08 14:32   ` Re: Unions and where optimisation Tomasz Myrta <jasiek@klaster.net>
@ 2003-01-08 14:36     ` Boris Klug <boris.klug@control.de>
  2003-01-08 15:48       ` Re: Unions and where optimisation Michael Paesold <mpaesold@gmx.at>
  1 sibling, 1 reply; 8+ messages in thread

From: Boris Klug @ 2003-01-08 14:36 UTC (permalink / raw)
  To: Tomasz Myrta <jasiek@klaster.net>; Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance


Hello!

> Hannu, does it work?
> Few months ago I lost some time trying to create this kind of query and
> I always got error, that subselect doesn't knows anything about upper
> (outer?) table.

It does not work on my PostgreSQL 7.2.x

Get the same error like you: "relation rk does not exist"

Also the disadvantage of this solution is that the speed up is bound to 
queries for the ordernr. If a statement has a where clause e.g. for a 
timestamp, the view is still slow.

Does PostgreSQL not know how to move where clause inside each select in a 
union?

-- 
Dipl. Inform. Boris Klug, control IT GmbH, Germany



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 16:02 ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
  2003-01-08 14:32   ` Re: Unions and where optimisation Tomasz Myrta <jasiek@klaster.net>
  2003-01-08 14:36     ` Re: Unions and where optimisation Boris Klug <boris.klug@control.de>
@ 2003-01-08 15:48       ` Michael Paesold <mpaesold@gmx.at>
  2003-01-08 16:13         ` Re: Unions and where optimisation Boris Klug <boris.klug@control.de>
  0 siblings, 1 reply; 8+ messages in thread

From: Michael Paesold @ 2003-01-08 15:48 UTC (permalink / raw)
  To: Boris Klug <boris.klug@control.de>; Tomasz Myrta <jasiek@klaster.net>; Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance

Boris Klug <boris.klug@control.de> wrote:

> > Hannu, does it work?
> > Few months ago I lost some time trying to create this kind of query and
> > I always got error, that subselect doesn't knows anything about upper
> > (outer?) table.
>
> It does not work on my PostgreSQL 7.2.x
>
> Get the same error like you: "relation rk does not exist"
>
> Also the disadvantage of this solution is that the speed up is bound to
> queries for the ordernr. If a statement has a where clause e.g. for a
> timestamp, the view is still slow.
>
> Does PostgreSQL not know how to move where clause inside each select in a
> union?

Hi Boris,

As far as I know, this has first been "fixed" in 7.3. I think it was Tom who
improved the optimizer to push the where clause into the selects of a union
view. I've done a test...

create view test as
   select updated, invoice_id from invoice
     union all
   select updated, invoice_id from inv2
     union all
   select updated, invoice_id from inv3;

... and it seems to work (postgresql 7.3 here):

billing=# explain select * from test where invoice_id = 111000;
                                         QUERY PLAN
----------------------------------------------------------------------------
----------------
 Subquery Scan test  (cost=0.00..413.24 rows=114 width=12)
   ->  Append  (cost=0.00..413.24 rows=114 width=12)
         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..6.00 rows=1 width=12)
               ->  Index Scan using pk_invoice on invoice  (cost=0.00..6.00
rows=1 width=12)
                     Index Cond: (invoice_id = 111000)
         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..203.62 rows=57
width=12)
               ->  Index Scan using idx_inv2 on inv2  (cost=0.00..203.62
rows=57 width=12)
                     Index Cond: (invoice_id = 111000)
         ->  Subquery Scan "*SELECT* 3"  (cost=0.00..203.62 rows=57
width=12)
               ->  Index Scan using idx_inv3 on inv3  (cost=0.00..203.62
rows=57 width=12)
                     Index Cond: (invoice_id = 111000)
(11 rows)

I hope this is helps. Can you upgrade to 7.3.1? I really think the upgrade
is worth the effort.

Best Regards,
Michael Paesold




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 16:02 ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
  2003-01-08 14:32   ` Re: Unions and where optimisation Tomasz Myrta <jasiek@klaster.net>
  2003-01-08 14:36     ` Re: Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 15:48       ` Re: Unions and where optimisation Michael Paesold <mpaesold@gmx.at>
@ 2003-01-08 16:13         ` Boris Klug <boris.klug@control.de>
  0 siblings, 0 replies; 8+ messages in thread

From: Boris Klug @ 2003-01-08 16:13 UTC (permalink / raw)
  To: Michael Paesold <mpaesold@gmx.at>; Tomasz Myrta <jasiek@klaster.net>; Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance

Hello!

> As far as I know, this has first been "fixed" in 7.3. I think it was Tom
> who improved the optimizer to push the where clause into the selects of a
> union view. I've done a test...

Yes, I installed 7.3 and it works fine there. I think we will upgrade to 7.3.1 
our development system soon.

Thank you!

-- 
Dipl. Inform. Boris Klug, control IT GmbH, Germany



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Unions and where optimisation
  2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
  2003-01-08 16:02 ` Re: Unions and where optimisation Hannu Krosing <hannu@tm.ee>
  2003-01-08 14:32   ` Re: Unions and where optimisation Tomasz Myrta <jasiek@klaster.net>
@ 2003-01-08 16:49     ` Hannu Krosing <hannu@tm.ee>
  1 sibling, 0 replies; 8+ messages in thread

From: Hannu Krosing @ 2003-01-08 16:49 UTC (permalink / raw)
  To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Boris Klug <boris.klug@control.de>; pgsql-performance

On Wed, 2003-01-08 at 14:32, Tomasz Myrta wrote:
> Hannu Krosing wrote:
> 
> >
> > 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.
> >
> 
> Hannu, does it work?

Nope! Sorry.

SQL spec clearly states that subqueries in FROM clause must not see each
other ;(

It would work in WITH part of the query, which will hopefully be
implemented in some future PG version, perhaps even 7.4 as WITH is the
prerequisite for implementing SQL99 recursive queries, and RedHat has
shown an strongish interest in implementing these.

> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
Hannu Krosing <hannu@tm.ee>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2003-01-08 16:49 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-08 13:25 Unions and where optimisation Boris Klug <boris.klug@control.de>
2003-01-08 14:40 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-08 16:02 ` Hannu Krosing <hannu@tm.ee>
2003-01-08 14:32   ` Tomasz Myrta <jasiek@klaster.net>
2003-01-08 14:36     ` Boris Klug <boris.klug@control.de>
2003-01-08 15:48       ` Michael Paesold <mpaesold@gmx.at>
2003-01-08 16:13         ` Boris Klug <boris.klug@control.de>
2003-01-08 16:49     ` Hannu Krosing <hannu@tm.ee>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox