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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
