Views with unions

From: Mariusz Czułada <manieq(at)idea(dot)net(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: Views with unions
Date: 2003-02-15 23:48:13
Message-ID: 200302160048.14681.manieq@idea.net.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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=1
union all
select key, value from tab2 where key=2
union all
select key, value from tab3 where key=3;

When querying with no conditions, I get plan:

test_db=# explain analyze select key, value from view123;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan view123 (cost=0.00..3.19 rows=15 width=11) (actual time=0.15..1.00 rows=15 loops=1)
-> Append (cost=0.00..3.19 rows=15 width=11) (actual time=0.14..0.80 rows=15 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.06 rows=5 width=11) (actual time=0.13..0.30 rows=5 loops=1)
-> Seq Scan on tab1 (cost=0.00..1.06 rows=5 width=11) (actual time=0.11..0.22 rows=5 loops=1)
Filter: ("key" = 1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.06 rows=5 width=11) (actual time=0.07..0.22 rows=5 loops=1)
-> Seq Scan on tab2 (cost=0.00..1.06 rows=5 width=11) (actual time=0.05..0.15 rows=5 loops=1)
Filter: ("key" = 2)
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.06 rows=5 width=11) (actual time=0.06..0.22 rows=5 loops=1)
-> Seq Scan on tab3 (cost=0.00..1.06 rows=5 width=11) (actual time=0.05..0.15 rows=5 loops=1)
Filter: ("key" = 3)
Total runtime: 1.57 msec
(12 rows)

But with "key = 3":

test_db# explain analyze select key, value from view123 where key=3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan view123 (cost=0.00..3.22 rows=7 width=11) (actual time=0.40..0.65 rows=5 loops=1)
-> Append (cost=0.00..3.22 rows=7 width=11) (actual time=0.38..0.58 rows=5 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.07 rows=1 width=11) (actual time=0.18..0.18 rows=0 loops=1)
-> Seq Scan on tab1 (cost=0.00..1.07 rows=1 width=11) (actual time=0.17..0.17 rows=0 loops=1)
Filter: (("key" = 1) AND ("key" = 3))
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
-> Seq Scan on tab2 (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
Filter: (("key" = 2) AND ("key" = 3))
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.07 rows=5 width=11) (actual time=0.08..0.25 rows=5 loops=1)
-> Seq Scan on tab3 (cost=0.00..1.07 rows=5 width=11) (actual time=0.06..0.18 rows=5 loops=1)
Filter: (("key" = 3) AND ("key" = 3))
Total runtime: 1.22 msec
(12 rows)

I would expect, that false filters, like (("key" = 1) AND ("key" = 3)) will make table full scan unnecessary. So I expected plan like:

test_db# explain analyze select key, value from view123 where key=3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Subquery Scan view123 (cost=0.00..3.22 rows=7 width=11) (actual time=0.40..0.65 rows=5 loops=1)
-> Append (cost=0.00..3.22 rows=7 width=11) (actual time=0.38..0.58 rows=5 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.07 rows=1 width=11) (actual time=0.18..0.18 rows=0 loops=1)
-> Result (cost=0.00..0.00 rows=0 width=11) (actual time=0.01..0.01 rows=0 loops=1)
^^^^^^^^^^^ my change
Filter: (("key" = 1) AND ("key" = 3)) [always false]
^^^^^^^^^^^ my change
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.07 rows=1 width=11) (actual time=0.11..0.11 rows=0 loops=1)
-> Result (cost=0.00..0.00 rows=0 width=11) (actual time=0.01..0.01 rows=0 loops=1)
^^^^^^^^^^^ my change
Filter: (("key" = 2) AND ("key" = 3)) [always false]
^^^^^^^^^^^ my change
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.07 rows=5 width=11) (actual time=0.08..0.25 rows=5 loops=1)
-> Seq Scan on tab3 (cost=0.00..1.07 rows=5 width=11) (actual time=0.06..0.18 rows=5 loops=1)
Filter: (("key" = 3) AND ("key" = 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łada

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2003-02-16 03:54:33 Re: Views with unions
Previous Message Scott Cain 2003-02-15 20:36:31 Re: [Gmod-schema] Re: performace problem after VACUUM