Another planner bug with subqueries
От | Matthew Gabeler-Lee |
---|---|
Тема | Another planner bug with subqueries |
Дата | |
Msg-id | ABABFB80F35AD311848B0090279918EF010B9B6A@ZYCOSNT2.hq.zycos.com обсуждение исходный текст |
Ответы |
Re: Another planner bug with subqueries
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: