Another planner bug with subqueries

When a view contains a subquery, sorted queries on the view don't put the
sort inside the view query like they should. 7.2.3 did it properly, and so
my most common query has now gone from <3s to several minutes.

Test case:

create table t1 (k int4 primary key, c1 int4);
create table t2 (k int4 primary key references t1, c2 int4);
create index x1 on t1 (c1);
create view v1 as select t1.k, c1, c2 from t1 natural join t2;
create view v2 as select t1.k, c1, c2, (select 'y') as v from t1 natural
join t2;

insert into t1 values (1, 2);
... insert incrementing values ...
insert into t1 values (10, 11);
insert into t2 values (1, 2);
... same idea ...
insert into t2 values (10, 11);

explain select * from v1 where c1 > 8 order by c1 desc limit 1 offset 0;
explain select * from v2 where c1 > 8 order by c1 desc limit 1 offset 0;
explain select t1.k, c1, c2, (select 'y') as v from t1 natural join t2 where
c1 > 8 order by c1 desc limit 1 offset 0;

The first and third queries get planned correctly (the third query is
pulling the view definition out into the main query), the second query gets
planned badly.

Planner output: for the 3 queries:

----------------------------
Limit (cost=0.00..4.97 rows=1 width=16)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=16)
-> Index Scan Backward using x1 on t1 (cost=0.00..45.50 rows=333
width=8)
Index Cond: (c1 > 8)
-> Index Scan using t2_pkey on t2 (cost=0.00..4.82 rows=1
width=8)
Index Cond: ("outer".k = t2.k)

----------------------------
Limit (cost=110.77..110.77 rows=1 width=16)
-> Sort (cost=110.77..111.60 rows=333 width=16)
Sort Key: c1
-> Subquery Scan v2 (cost=36.47..96.80 rows=333 width=16)
-> Merge Join (cost=36.47..96.80 rows=333 width=16)
Merge Cond: ("outer".k = "inner".k)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Index Scan using t2_pkey on t2 (cost=0.00..52.00
rows=1000 width=8)
-> Sort (cost=36.47..37.30 rows=333 width=8)
Sort Key: t1.k
-> Seq Scan on t1 (cost=0.00..22.50 rows=333
width=8)
Filter: (c1 > 8)

----------------------------
Limit (cost=0.00..4.97 rows=1 width=16)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=16)
-> Index Scan Backward using x1 on t1 (cost=0.00..45.50 rows=333
width=8)
Index Cond: (c1 > 8)
-> Index Scan using t2_pkey on t2 (cost=0.00..4.82 rows=1
width=8)
Index Cond: ("outer".k = t2.k)

-Matt

Matthew Gabeler-Lee <mgabelerlee(at)zycos(dot)com> writes:
> When a view contains a subquery, sorted queries on the view don't put the
> sort inside the view query like they should. 7.2.3 did it properly, and so
> my most common query has now gone from <3s to several minutes.

This is a consequence of my quick-hack solution to your previous
problem: views with subselects in their targetlist will not be
flattened.

I'm afraid there's not much to be done about it for 7.3.* --- AFAICS,
a proper fix would be too big and too risky to put into the stable
branch. The correct long-term fix (flatten join alias vars earlier)
has to wait until we do some fundamental planner work, and I don't
see any other safe quick-hack answer.

regards, tom lane