Обсуждение: Optimizer bug in subselect/view

I have in subselect processing.

Example below:

create table users (uid serial primary key, uname text);

create table folders (fid serial primary key, fname text, uid int);

create view user_view as select u.uid, u.uname, (select count (*) from
folders where uid = u.uid) as nfolders from users u;

explain select uname from user_view where uid = '1';

In PostgreSQL version <= 7.3 I got following:

Index Scan using users_pkey on users u  (cost=0.00..4.82 rows=1
width=32)

PostgreSQL version > 7.3 (7.3.1, 7.3.2) shows me:

 Subquery Scan user_view  (cost=0.00..4.82 rows=1 width=36)
   ->  Index Scan using users_pkey on users u  (cost=0.00..4.82 rows=1
width=36)
         Index Cond: (uid = 1)
         SubPlan
           ->  Aggregate  (cost=22.51..22.51 rows=1 width=0)
                 ->  Seq Scan on folders  (cost=0.00..22.50 rows=5
width=0)
                       Filter: (uid = $0)

As you can see, optimizer in earlier version of PostgreSQL skips
unneeded data, but latest version doesn't.
This behavior can slow down query execition, especially if subselect is
time consuming and rarely used.

--
with best wishes

Andrew Koshelev        System Administrator
          mailto:andrew@sgg.ru

Andrew Koshelev <andrew@sgg.ru> writes:
> As you can see, optimizer in earlier version of PostgreSQL skips
> unneeded data, but latest version doesn't.

CVS tip works the way you want it to.  The behavior in 7.3.* is an
unfortunate side-effect of a quick-and-dirty bug fix.  The less dirty
way to fix it is in CVS tip but was deemed too risky to backpatch
into 7.3.*.

            regards, tom lane