From: Litao Wu <litaowu@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: sunquery and estimated rows
Date: Mon, 19 Apr 2004 09:26:03 -0700 (PDT)
Message-ID: <20040419162603.31251.qmail@web13121.mail.yahoo.com> (raw)
In-Reply-To: <1082316145.2191.0.camel@yarrow.bertheau.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: litaowu@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: sunquery and estimated rows
In-Reply-To: <20040419162603.31251.qmail@web13121.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox