Message-ID: <42DE6CD5.8070100@aeccom.com>
Date: Wed, 20 Jul 2005 17:25:09 +0200
From: =?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= <lutzeb@aeccom.com>
Organization: AEC/communications GmbH
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Optimizer seems to be way off, why?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I do not under stand the following explain output (pgsql 8.0.3):

explain analyze
select b.e from b, d
where b.r=516081780 and b.c=513652057 and b.e=d.e;

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1220.09 rows=1 width=4) (actual 
time=0.213..2926.845 rows=324503 loops=1)
   ->  Index Scan using b_index on b  (cost=0.00..1199.12 rows=1 
width=4) (actual time=0.104..17.418 rows=3293 loops=1)
         Index Cond: (r = 516081780::oid)
         Filter: (c = 513652057::oid)
   ->  Index Scan using d_e_index on d  (cost=0.00..19.22 rows=140 
width=4) (actual time=0.009..0.380 rows=99 loops=3293)
         Index Cond: ("outer".e = d.e)
 Total runtime: 3638.783 ms
(7 rows)

Why is the rows estimate for b_index and the nested loop 1? It is 
actually 3293 and 324503.

I did VACUUM ANALYZE before and I also increased the STATISTICS TARGET 
on b.e to 500. No change.

Here is the size of the tables:

select count(oid) from b;
 3532161

select count(oid) from b where r=516081780 and c=513652057;
  3293

select count(oid) from d;
 117270


Regards,

Dirk