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 по дате отправления:

Предыдущее
От: Chris Gamache
Дата:
Сообщение: Re: pg_dump 2 gig file size limit on ext3
Следующее
От: Joe Conway
Дата:
Сообщение: Re: the "/usr/local/pgsql/data" directory size