pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mariusz Czułada <manieq@idea.net.pl>
To: pgsql-performance@postgresql.org
Subject: Views with unions
Date: Sun, 16 Feb 2003 00:48:13 +0100
Message-ID: <200302160048.14681.manieq@idea.net.pl> (raw)

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



view thread (13+ messages)  latest in thread

Message-ID: <200302160048.14681.manieq@idea.net.pl>
Permalink:  ../200302160048.14681.manieq@idea.net.pl/
Also on:    postgresql.org/message-id/200302160048.14681.manieq@idea.net.pl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: manieq@idea.net.pl
  Subject: Re: Views with unions
  In-Reply-To: <200302160048.14681.manieq@idea.net.pl>
  References: <200302160048.14681.manieq@idea.net.pl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox