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