Message-ID: <42DE8375.8020205@archonet.com>
Date: Wed, 20 Jul 2005 18:01:41 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= <lutzeb@aeccom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer seems to be way off, why?
References: <42DE6CD5.8070100@aeccom.com>
In-Reply-To: <42DE6CD5.8070100@aeccom.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Dirk Lutzeb=E4ck wrote:
> Hi,
>=20
> I do not under stand the following explain output (pgsql 8.0.3):
>=20
> explain analyze
> select b.e from b, d
> where b.r=3D516081780 and b.c=3D513652057 and b.e=3Dd.e;
>=20
>                                                         QUERY PLAN
> -----------------------------------------------------------------------=
-----------------------------------------=20
>=20
> Nested Loop  (cost=3D0.00..1220.09 rows=3D1 width=3D4) (actual=20
> time=3D0.213..2926.845 rows=3D324503 loops=3D1)
>   ->  Index Scan using b_index on b  (cost=3D0.00..1199.12 rows=3D1 wid=
th=3D4)=20
> (actual time=3D0.104..17.418 rows=3D3293 loops=3D1)
>         Index Cond: (r =3D 516081780::oid)
>         Filter: (c =3D 513652057::oid)
>   ->  Index Scan using d_e_index on d  (cost=3D0.00..19.22 rows=3D140=20
> width=3D4) (actual time=3D0.009..0.380 rows=3D99 loops=3D3293)
>         Index Cond: ("outer".e =3D d.e)
> Total runtime: 3638.783 ms
> (7 rows)
>=20
> Why is the rows estimate for b_index and the nested loop 1? It is=20
> actually 3293 and 324503.

I'm guessing (and that's all it is) that b.r and b.c have a higher=20
correlation than the planner is expecting. That is, it expects the=20
b.c=3D... to reduce the number of matching rows much more than it is.

Try a query just on WHERE b.r=3D516081780 and see if it gets the estimate=
=20
right for that.

If it's a common query, it might be worth an index on (r,c)

--
   Richard Huxton
   Archonet Ltd