Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1AD78475F34 for ; Wed, 8 Jan 2003 08:27:08 -0500 (EST) Received: from benhur.intern.control.de (unknown [217.89.112.10]) by postgresql.org (Postfix) with ESMTP id C1AA0475CED for ; Wed, 8 Jan 2003 08:27:06 -0500 (EST) Received: from devserver (devserver.intern.control.de [192.168.100.5]) by benhur.intern.control.de (8.12.6/8.12.6/Benhur 8.12.6) with ESMTP id h08DQGVO019858 for ; Wed, 8 Jan 2003 14:26:17 +0100 Content-Type: text/plain; charset="us-ascii" From: Boris Klug Organization: control IT GmbH To: pgsql-performance@postgresql.org Subject: Unions and where optimisation Date: Wed, 8 Jan 2003 14:25:48 +0100 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200301081425.48597.boris.klug@control.de> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/52 X-Sequence-Number: 699 Hello! I am quite new in the PostgreSQL performance business, done a few years Ora= cle=20 stuff before. My ist question is the following: We have three table, lets name them rk150, 151 and rk152. They all have a= =20 timestamp and a order number in common but than different data after this.= =20 Now I need the data from all tables in one view for a given order number, s= o=20 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=3D'1234'" it takes ove= r 14=20 seconds! The problem is now that PostgreSQL first does the union with all the three= =20 tables and after this sorts out the right rows: Subquery Scan a (cost=3D54699.06..56622.18 rows=3D38462 width=3D20) -> Unique (cost=3D54699.06..56622.18 rows=3D38462 width=3D20) -> Sort (cost=3D54699.06..54699.06 rows=3D384624 width=3D20) -> Append (cost=3D0.00..10689.24 rows=3D384624 width=3D20) -> Subquery Scan *SELECT* 1 (cost=3D0.00..8862.52 rows=3D314852 width=3D20) -> Seq Scan on rk150=20 (cost=3D0.00..8862.52 rows=3D314852 width=3D20) -> Subquery Scan *SELECT* 2=20=20 (cost=3D0.00..1208.58 rows=3D45858 width=3D20) -> Seq Scan on rk151=20=20 (cost=3D0.00..1208.58 rows=3D45858 width=3D20) -> Subquery Scan *SELECT* 3=20 (cost=3D0.00..618.14 rows=3D23914 width=3D20) -> Seq Scan on rk152=20=20 (cost=3D0.00..618.14 rows=3D23914 width=3D20) A better thing would it (Oracle does this and I think I have seen it on=20 PostgreSQL before), that the where-clause is moved inside every select so w= e=20 have something like this (written by hand): select * from ( select zeit, aufnr from rk150 where aufnr=3D'13153811' union select zeit, aufnr from rk151 where aufnr=3D'13153811' union select zeit, aufnr from rk152 where aufnr=3D'13153811') as A; This takes less than 1 second because the nr of rows that have to be joined= =20 are only 45 (optimizer expects 4), not > 300.000: Subquery Scan a (cost=3D45.97..46.19 rows=3D4 width=3D20) -> Unique (cost=3D45.97..46.19 rows=3D4 width=3D20) -> Sort (cost=3D45.97..45.97 rows=3D45 width=3D20) -> Append (cost=3D0.00..44.74 rows=3D45 width=3D20) -> Subquery Scan *SELECT* 1=20 (cost=3D0.00..32.22 rows=3D31 width=3D20) -> Index Scan using rk150_uidx_aufnr on rk150=20= =20 (cost=3D0.00..32.22 rows=3D31 width=3D20) -> Subquery Scan *SELECT* 2=20 (cost=3D0.00..7.67 rows=3D9 width=3D20) -> Index Scan using rk151_uidx_aufnr on rk151=20= =20 (cost=3D0.00..7.67 rows=3D9 width=3D20) -> Subquery Scan *SELECT* 3=20=20 (cost=3D0.00..4.85 rows=3D5 width=3D20) -> Index Scan using rk152_uidx_aufnr on rk152=20 (cost=3D0.00..4.85 rows=3D5 width=3D20) My question now: Is the optimizer able to move the where clause into unions= ?=20 If so, how I can get him to do it? Thank you for the help in advance! --=20 Dipl. Inform. Boris Klug, control IT GmbH, Germany