Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group