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

I encounter bug 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 data is rarely used.

--
with best wishes

Andrew Koshelev        System Administrator
          mailto:andrew@sgg.ru