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
copy link · copy postgr.es
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