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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
