From: Litao Wu <litaowu@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: sunquery and estimated rows
Date: Fri, 16 Apr 2004 14:45:29 -0700 (PDT)
Message-ID: <20040416214529.10468.qmail@web13125.mail.yahoo.com> (raw)
Hi,
When I included a subquery, the estimated rows (1240)
is way too high as shown in the following example.
Can someone explain why? Because of this behavior,
some of our queries use hash join instead of nested
loop.
Thanks,
select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by
GCC 2.96
(1 row)
\d test
Table "public.test"
Column | Type | Modifiers
---------+--------------------------+-----------
id | integer |
name | character varying(255) |
d_id | integer |
c_id | integer |
r_id | integer |
u_id | integer |
scope | integer |
active | integer |
created | timestamp with time zone |
typ | integer |
Indexes: test_scope_idx btree (scope)
reindex table test;
vacuum full analyze test;
select count(*) from test;
count
-------
4959
(1 row)
select count(*) from test where scope=10;
count
-------
10
(1 row)
explain analyze
select * from test
where scope=10; -- so far so good, estimate 12 rows,
actual 10 rows
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using test_scope_idx on test
(cost=0.00..4.35 rows=12 width=59) (actual
time=0.04..0.11 rows=10 loops=1)
Index Cond: (scope = 10)
Total runtime: 0.23 msec
(3 rows)
explain analyze
select * from test
where scope=(select 10); -- estimate rows is way too
high, do not why????
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using test_scope_idx on test
(cost=0.00..40.74 rows=1240 width=59) (actual
time=0.06..0.13 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.22 msec
(5 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
Subject: Re: sunquery and estimated rows
In-Reply-To: <20040416214529.10468.qmail@web13125.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