X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 61549474E61 for ; Sat, 15 Feb 2003 18:48:03 -0500 (EST) Received: from mail.idea.net.pl (smtp-in.centertel.pl [194.9.223.8]) by postgresql.org (Postfix) with ESMTP id B820C474E42 for ; Sat, 15 Feb 2003 18:48:02 -0500 (EST) Received: from skorupa (vb145.neoplus.adsl.tpnet.pl [80.50.229.145]) by mta3.centertel.pl (iPlanet Messaging Server 5.2 (built Feb 21 2002)) with ESMTPA id <0HAD00AHZISKEF@mta3.centertel.pl> for pgsql-performance@postgresql.org; Sun, 16 Feb 2003 00:33:58 +0100 (CET) Date: Sun, 16 Feb 2003 00:48:13 +0100 From: Mariusz =?iso-8859-2?q?Czu=B3ada?= Subject: Views with unions To: pgsql-performance@postgresql.org Message-id: <200302160048.14681.manieq@idea.net.pl> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-2 Content-transfer-encoding: quoted-printable Content-disposition: inline User-Agent: KMail/1.6.1-cool X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/95 X-Sequence-Number: 1151 Hello, While testing multi-select views I found some problems. Here are details. I= have 3 tables and I created a view on them: create view view123 as select key, value from tab1 where key=3D1 union all select key, value from tab2 where key=3D2 union all select key, value from tab3 where key=3D3; When querying with no conditions, I get plan: test_db=3D# explain analyze select key, value from view123; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------- Subquery Scan view123 (cost=3D0.00..3.19 rows=3D15 width=3D11) (actual ti= me=3D0.15..1.00 rows=3D15 loops=3D1) -> Append (cost=3D0.00..3.19 rows=3D15 width=3D11) (actual time=3D0.14= ..0.80 rows=3D15 loops=3D1) -> Subquery Scan "*SELECT* 1" (cost=3D0.00..1.06 rows=3D5 width= =3D11) (actual time=3D0.13..0.30 rows=3D5 loops=3D1) -> Seq Scan on tab1 (cost=3D0.00..1.06 rows=3D5 width=3D11= ) (actual time=3D0.11..0.22 rows=3D5 loops=3D1) Filter: ("key" =3D 1) -> Subquery Scan "*SELECT* 2" (cost=3D0.00..1.06 rows=3D5 width= =3D11) (actual time=3D0.07..0.22 rows=3D5 loops=3D1) -> Seq Scan on tab2 (cost=3D0.00..1.06 rows=3D5 width=3D11= ) (actual time=3D0.05..0.15 rows=3D5 loops=3D1) Filter: ("key" =3D 2) -> Subquery Scan "*SELECT* 3" (cost=3D0.00..1.06 rows=3D5 width= =3D11) (actual time=3D0.06..0.22 rows=3D5 loops=3D1) -> Seq Scan on tab3 (cost=3D0.00..1.06 rows=3D5 width=3D11= ) (actual time=3D0.05..0.15 rows=3D5 loops=3D1) Filter: ("key" =3D 3) Total runtime: 1.57 msec (12 rows) But with "key =3D 3": test_db# explain analyze select key, value from view123 where key=3D3; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------- Subquery Scan view123 (cost=3D0.00..3.22 rows=3D7 width=3D11) (actual tim= e=3D0.40..0.65 rows=3D5 loops=3D1) -> Append (cost=3D0.00..3.22 rows=3D7 width=3D11) (actual time=3D0.38.= .0.58 rows=3D5 loops=3D1) -> Subquery Scan "*SELECT* 1" (cost=3D0.00..1.07 rows=3D1 width= =3D11) (actual time=3D0.18..0.18 rows=3D0 loops=3D1) -> Seq Scan on tab1 (cost=3D0.00..1.07 rows=3D1 width=3D11= ) (actual time=3D0.17..0.17 rows=3D0 loops=3D1) Filter: (("key" =3D 1) AND ("key" =3D 3)) -> Subquery Scan "*SELECT* 2" (cost=3D0.00..1.07 rows=3D1 width= =3D11) (actual time=3D0.11..0.11 rows=3D0 loops=3D1) -> Seq Scan on tab2 (cost=3D0.00..1.07 rows=3D1 width=3D11= ) (actual time=3D0.11..0.11 rows=3D0 loops=3D1) Filter: (("key" =3D 2) AND ("key" =3D 3)) -> Subquery Scan "*SELECT* 3" (cost=3D0.00..1.07 rows=3D5 width= =3D11) (actual time=3D0.08..0.25 rows=3D5 loops=3D1) -> Seq Scan on tab3 (cost=3D0.00..1.07 rows=3D5 width=3D11= ) (actual time=3D0.06..0.18 rows=3D5 loops=3D1) Filter: (("key" =3D 3) AND ("key" =3D 3)) Total runtime: 1.22 msec (12 rows) I would expect, that false filters, like (("key" =3D 1) AND ("key" =3D 3)) = will make table full scan unnecessary. So I expected plan like: test_db# explain analyze select key, value from view123 where key=3D3; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------- Subquery Scan view123 (cost=3D0.00..3.22 rows=3D7 width=3D11) (actual tim= e=3D0.40..0.65 rows=3D5 loops=3D1) -> Append (cost=3D0.00..3.22 rows=3D7 width=3D11) (actual time=3D0.38.= .0.58 rows=3D5 loops=3D1) -> Subquery Scan "*SELECT* 1" (cost=3D0.00..1.07 rows=3D1 width= =3D11) (actual time=3D0.18..0.18 rows=3D0 loops=3D1) -> Result (cost=3D0.00..0.00 rows=3D0 width=3D11) (actual = time=3D0.01..0.01 rows=3D0 loops=3D1) ^^^^^^^^^^^ my change Filter: (("key" =3D 1) AND ("key" =3D 3)) [always fals= e] = ^^^^^^^^^^^ my change -> Subquery Scan "*SELECT* 2" (cost=3D0.00..1.07 rows=3D1 width= =3D11) (actual time=3D0.11..0.11 rows=3D0 loops=3D1) -> Result (cost=3D0.00..0.00 rows=3D0 width=3D11) (actual = time=3D0.01..0.01 rows=3D0 loops=3D1) ^^^^^^^^^^^ my change Filter: (("key" =3D 2) AND ("key" =3D 3)) [always fals= e] = ^^^^^^^^^^^ my change -> Subquery Scan "*SELECT* 3" (cost=3D0.00..1.07 rows=3D5 width= =3D11) (actual time=3D0.08..0.25 rows=3D5 loops=3D1) -> Seq Scan on tab3 (cost=3D0.00..1.07 rows=3D5 width=3D11= ) (actual time=3D0.06..0.18 rows=3D5 loops=3D1) Filter: (("key" =3D 3) AND ("key" =3D 3)) Total runtime: 1.22 msec (12 rows) No "Seq Scan" on tables where filter is false. I realize that's how it works now, but: a) is there any way to avoid such scans? b) is it possible (or in TODO) to optimize for such cases? Regards, Mariusz Czu=B3ada