Message-ID: <42DEBACF.2020405@arbash-meinel.com>
Date: Wed, 20 Jul 2005 15:57:51 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: lutzeb@aeccom.com
Cc: Richard Huxton <dev@archonet.com>,
	pgsql-performance@postgresql.org
Subject: Re: Optimizer seems to be way off, why?
References: <42DE6CD5.8070100@aeccom.com> <42DE8375.8020205@archonet.com>
	<42DEA308.6030608@aeccom.com>
In-Reply-To: <42DEA308.6030608@aeccom.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig94CE97DBB20D147FCE6A8757"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig94CE97DBB20D147FCE6A8757
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Dirk Lutzeb=E4ck wrote:
> Richard Huxton wrote:
>=20
>> Dirk Lutzeb=E4ck wrote:
>>
>>> 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=3D516081780 and b.c=3D513652057 and b.e=3Dd.e;
>>>
>>>                                                         QUERY PLAN
>>> ---------------------------------------------------------------------=
-------------------------------------------
>>>
>>> Nested Loop  (cost=3D0.00..1220.09 rows=3D1 width=3D4) (actual
>>> time=3D0.213..2926.845 rows=3D324503 loops=3D1)
>>>   ->  Index Scan using b_index on b  (cost=3D0.00..1199.12 rows=3D1
>>> width=3D4) (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=

>>> 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)
>>>
>>> Why is the rows estimate for b_index and the nested loop 1? It is
>>> actually 3293 and 324503.
>>
>>
>>
>> I'm guessing (and that's all it is) that b.r and b.c have a higher
>> correlation than the planner is expecting. That is, it expects the
>> 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 right for that.
>>
>> If it's a common query, it might be worth an index on (r,c)
>>
>> --=20
>>   Richard Huxton
>>   Archonet Ltd
>>
>=20
> Thanks Richard, dropping the join for b.c now gives better estimates (i=
t
> also uses a different index now) although not accurate (off by factor
> 10). This query is embedded in a larger query which now got a 1000 time=
s
> speed up (!) because I can drop b.c because it is redundant.

Well, part of the problem is that the poorly estimated row is not 'b.e'
but 'b.r', it expects to only find one row that matches, and instead
finds 3293 rows.

Now, that *could* be because it mis-estimates the selectivity of b.r & b.=
c.

It actually estimated the join with d approximately correctly. (It
thought that for each row it would find 140, and it averaged 99).

>=20
> Though, why can't the planner see this correlation? I think somebody
> said the planner does not know about multiple column correlations, does=
 it?

The planner does not maintain cross-column statistics, so you are
correct. I believe it assumes distributions are independent. So that if
r=3DRRRRR is 10% selective, and c=3DCCCC is 20% selective, the total
selectivity of r=3DRRRR AND c=3DCCCC is 2%. I could be wrong on this, but=
 I
think it is approximately correct.

Now if you created the index on b(r,c), then it would have a much better
idea of how selective that would be. At the very least, it could index
on (r,c) rather than indexing on (r) and filtering by (c).

Also, if you have very skewed data (where you have 1 value 100k times,
and 50 values only 10times each), the planner can overestimate the low
values, and underestimate the high one. (It uses random sampling, so it
kind of depends where the entries are.)

Have you tried increasing the statistics on b.r and or b.c? Do you have
an index on b.c or just b.r?

To see what the planner thinks, you might try:

EXPLAIN ANALYZE
select count(*) from b where r=3D516081780;

That would tell you how selective the planner thinks the r=3D is.
>=20
> Regards,
>=20
> Dirk
>=20
John
=3D:->


--------------enig94CE97DBB20D147FCE6A8757
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC3rrTJdeBCYSNAAMRAihcAJ4ime7NRUFh5qAs5gueJTu6mx5YagCgmYuo
+OUMPNe8fv5k22wO96NKP5U=
=7G8I
-----END PGP SIGNATURE-----

--------------enig94CE97DBB20D147FCE6A8757--