BUG #7785: Bad plan for UNION ALL view containing JOIN

The following bug has been logged on the website:

Bug reference: 7785
Logged by: Dag Lem
Email address: dag(at)nimrod(dot)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)