Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 70FEE475D70 for ; Wed, 8 Jan 2003 10:47:40 -0500 (EST) Received: from mail.gmx.net (mail.gmx.net [213.165.65.60]) by postgresql.org (Postfix) with SMTP id 5ED75475CED for ; Wed, 8 Jan 2003 10:47:39 -0500 (EST) Received: (qmail 30675 invoked by uid 0); 8 Jan 2003 15:47:43 -0000 Received: from chello062178186201.1.15.tuwien.teleweb.at (HELO beeblebrox) (62.178.186.201) by mail.gmx.net (mp006-rz3) with SMTP; 8 Jan 2003 15:47:43 -0000 Message-ID: <013001c2b72d$658ee4b0$3201a8c0@beeblebrox> From: "Michael Paesold" To: "Boris Klug" , "Tomasz Myrta" , "Hannu Krosing" Cc: References: <200301081425.48597.boris.klug@control.de> <1042041735.3237.1.camel@huli> <3E1C366B.5080406@klaster.net> <200301081536.33211.boris.klug@control.de> Subject: Re: Unions and where optimisation Date: Wed, 8 Jan 2003 16:48:27 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/60 X-Sequence-Number: 707 Boris Klug 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