The following bug has been logged on the website:
Bug reference: 7785
Logged by: Dag Lem
Email address: d...@nimrod.no
PostgreSQL version: 9.2.1
Operating system: RedHat EL 5.8
Description:
Hi,
The test case below demonstrates that the planner pushes down the WHERE (ON)
clause in a UNION ALL view, but fails to push down the WHERE (ON) clause
when a JOIN clause is introduced in the view.
Any simple fix? :-)
Best regards,
Dag Lem
create table a2 (
pk integer primary key,
val2 integer
);
create table a1 (
pk integer primary key,
val1 integer,
pk_a2 integer
);
create table b (
pk integer primary key,
val1 integer,
val2 integer
);
create table c (
pk integer
);
insert into a2 select generate_series(1,10000), random()*1000;
insert into a1 select s, random(), s%20000 from generate_series(1,990000)
s;
insert into b select generate_series(1100001, 2000000), random()*1000;
insert into c select generate_series(980001, 1020000);
analyze a1;
analyze a2;
analyze b;
analyze c;
create view ab_good as
select a1.pk, val1
from a1
union all
select pk, val1
from b;
create view ab_bad as
select a1.pk, val1, val2
from a1
left join a2
on (a2.pk = a1.pk)
union all
select pk, val1, val2
from b;
explain analyze
select ab.pk, ab.val1
from c
left join ab_good ab
on (ab.pk = c.pk);
explain analyze
select ab.pk, ab.val1, ab.val2
from c
left join ab_bad ab
on (ab.pk = c.pk);
*** ab_good - Good query plan ***
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------
Nested Loop Left Join (cost=0.00..75682.00 rows=1890000 width=8) (actual
time=
0.037..702.184 rows=40000 loops=1)
-> Seq Scan on c (cost=0.00..577.00 rows=40000 width=4) (actual
time=0.011.
.50.068 rows=40000 loops=1)
-> Append (cost=0.00..1.86 rows=2 width=8) (actual time=0.010..0.012
rows=0
loops=40000)
-> Index Scan using a1_pkey on a1 (cost=0.00..0.98 rows=1
width=8) (a
ctual time=0.003..0.004 rows=0 loops=40000)
Index Cond: (pk = c.pk)
-> Index Scan using b_pkey on b (cost=0.00..0.88 rows=1 width=8)
(act
ual time=0.003..0.003 rows=0 loops=40000)
Index Cond: (pk = c.pk)
Total runtime: 750.259 ms
(8 rows)
*** ab_bad - Bad query plan ***
QUERY PLAN
--------------------------------------------------------------------------------
---------------------------------------------------------------
Hash Right Join (cost=1077.64..98329.41 rows=1890000 width=12) (actual
time=85
58.418..13902.096 rows=40000 loops=1)
Hash Cond: (a1.pk = c.pk)
-> Append (cost=0.64..50002.41 rows=1890000 width=12) (actual
time=0.054..9
710.313 rows=1890000 loops=1)
-> Merge Left Join (cost=0.64..27119.41 rows=990000 width=12)
(actual
time=0.050..4057.602 rows=990000 loops=1)
Merge Cond: (a1.pk = a2.pk)
-> Index Scan using a1_pkey on a1 (cost=0.00..24278.66
rows=990
000 width=8) (actual time=0.024..1575.131 rows=990000 loops=1)
-> Index Scan using a2_pkey on a2 (cost=0.00..240.75
rows=10000
width=8) (actual time=0.015..15.589 rows=10000 loops=1)
-> Seq Scan on b (cost=0.00..12983.00 rows=900000 width=12)
(actual t
ime=0.011..1119.213 rows=900000 loops=1)
-> Hash (cost=577.00..577.00 rows=40000 width=4) (actual
time=103.728..103.
728 rows=40000 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 1407kB
-> Seq Scan on c (cost=0.00..577.00 rows=40000 width=4) (actual
time=
0.011..49.449 rows=40000 loops=1)
Total runtime: 13950.038 ms
(12 rows)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
