BUG #7785: Bad plan for UNION ALL view containing JOIN - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7785
Logged by:          Dag Lem
Email address:      dag@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 =3D 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 =3D c.pk);

explain analyze
select ab.pk, ab.val1, ab.val2
from c
left join ab_bad ab
  on (ab.pk =3D c.pk);


*** ab_good - Good query plan ***
                                                         QUERY PLAN         =

   =

                                            =

---------------------------------------------------------------------------=
-----
--------------------------------------------
 Nested Loop Left Join  (cost=3D0.00..75682.00 rows=3D1890000 width=3D8) (a=
ctual
time=3D
0.037..702.184 rows=3D40000 loops=3D1)
   ->  Seq Scan on c  (cost=3D0.00..577.00 rows=3D40000 width=3D4) (actual
time=3D0.011.
.50.068 rows=3D40000 loops=3D1)
   ->  Append  (cost=3D0.00..1.86 rows=3D2 width=3D8) (actual time=3D0.010.=
.0.012
rows=3D0
 loops=3D40000)
         ->  Index Scan using a1_pkey on a1  (cost=3D0.00..0.98 rows=3D1
width=3D8) (a
ctual time=3D0.003..0.004 rows=3D0 loops=3D40000)
               Index Cond: (pk =3D c.pk)
         ->  Index Scan using b_pkey on b  (cost=3D0.00..0.88 rows=3D1 widt=
h=3D8)
(act
ual time=3D0.003..0.003 rows=3D0 loops=3D40000)
               Index Cond: (pk =3D c.pk)
 Total runtime: 750.259 ms
(8 rows)



*** ab_bad - Bad query plan ***
                                                                  QUERY PLAN
   =

                                                               =

---------------------------------------------------------------------------=
-----
---------------------------------------------------------------
 Hash Right Join  (cost=3D1077.64..98329.41 rows=3D1890000 width=3D12) (act=
ual
time=3D85
58.418..13902.096 rows=3D40000 loops=3D1)
   Hash Cond: (a1.pk =3D c.pk)
   ->  Append  (cost=3D0.64..50002.41 rows=3D1890000 width=3D12) (actual
time=3D0.054..9
710.313 rows=3D1890000 loops=3D1)
         ->  Merge Left Join  (cost=3D0.64..27119.41 rows=3D990000 width=3D=
12)
(actual
 time=3D0.050..4057.602 rows=3D990000 loops=3D1)
               Merge Cond: (a1.pk =3D a2.pk)
               ->  Index Scan using a1_pkey on a1  (cost=3D0.00..24278.66
rows=3D990
000 width=3D8) (actual time=3D0.024..1575.131 rows=3D990000 loops=3D1)
               ->  Index Scan using a2_pkey on a2  (cost=3D0.00..240.75
rows=3D10000
 width=3D8) (actual time=3D0.015..15.589 rows=3D10000 loops=3D1)
         ->  Seq Scan on b  (cost=3D0.00..12983.00 rows=3D900000 width=3D12)
(actual t
ime=3D0.011..1119.213 rows=3D900000 loops=3D1)
   ->  Hash  (cost=3D577.00..577.00 rows=3D40000 width=3D4) (actual
time=3D103.728..103.
728 rows=3D40000 loops=3D1)
         Buckets: 4096  Batches: 1  Memory Usage: 1407kB
         ->  Seq Scan on c  (cost=3D0.00..577.00 rows=3D40000 width=3D4) (a=
ctual
time=3D
0.011..49.449 rows=3D40000 loops=3D1)
 Total runtime: 13950.038 ms
(12 rows)