Re: sunquery and estimated rows

Well, the example shown is simplified version.
Now, let's see a little 'real' example (still
simplified version):

Table test is same as before:
\d test
              Table "public.test"
 Column  |           Type           | Modifiers
---------+--------------------------+-----------
 id      | integer                  |
 ...
 scope   | integer                  |
 ...
Indexes: test_scope_idx btree (scope)

select count(*) from test;
 count
-------
  4959
(1 row)
select count(*) from test where scope=10;
 count
-------
    10
(1 row)

create table scope_def (scope int primary key, name
varchar(30) unique);
insert into scope_def values (10, 'TEST_SCOPE');

-- This is not a trivial arithmetic expression
explain analyze
select * from test
where scope=(select scope from scope_def where name =
'TEST_SCOPE');

-- estimated row is 1653, returned rows is 10


     QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test
(cost=0.00..49.91 rows=1653 width=59) (actual
time=0.08..0.15 rows=10 loops=1)
   Index Cond: (scope = $0)
   InitPlan
     ->  Index Scan using scope_def_name_key on
scope_def  (cost=0.00..4.82 rows=1 width=4) (actual
time=0.04..0.04 rows=1 loops=1)
           Index Cond: (name = 'TEST_SCOPE'::character
varying)
 Total runtime: 0.22 msec
(6 rows)


-- trivial arithmetic expression
-- estimated row is 1653, returned rows is 10
explain analyze
select * from test
where scope=(select 10);

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test
(cost=0.00..49.91 rows=1653 width=59) (actual
time=0.06..0.14 rows=10 loops=1)
   Index Cond: (scope = $0)
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
(actual time=0.01..0.01 rows=1 loops=1)
 Total runtime: 0.20 msec
(5 rows)

-- This is the plan I expect to see: estimated rows is
-- close the actual returned rows.
-- Do I have to devide the sub-select into two
-- queries?

explain analyze
select * from test
where scope=10;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test
(cost=0.00..3.77 rows=10 width=59) (actual
time=0.05..0.12 rows=10 loops=1)
   Index Cond: (scope = 10)
 Total runtime: 0.18 msec
(3 rows)

-- Rewritten query using join in this case
explain analyze
select test.* from test JOIN scope_def using (scope)
where scope_def.name = 'TEST_SCOPE';

     QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..75.39 rows=5 width=63)
(actual time=0.07..0.19 rows=10 loops=1)
   ->  Index Scan using scope_def_name_key on
scope_def  (cost=0.00..4.82 rows=1 width=4) (actual
time=0.04..0.04 rows=1 loops=1)
         Index Cond: (name = 'TEST_SCOPE'::character
varying)
   ->  Index Scan using test_scope_idx on test
(cost=0.00..49.91 rows=1653 width=59) (actual
time=0.02..0.09 rows=10 loops=1)
         Index Cond: (test.scope = "outer".scope)
 Total runtime: 0.28 msec
(6 rows)





__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html