Message-ID: <42D7379F.3040806@arbash-meinel.com>
Date: Thu, 14 Jul 2005 23:12:15 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow joining very large table to smaller ones
References: <46AF8F35-B735-4DFA-BA0A-AC82B5DA3CCE@drivefaster.net>
	<42D687E5.7080202@arbash-meinel.com>
	<C3470621-9DB0-444A-BAD1-F9995E0142FB@drivefaster.net>
	<42D6F177.3000002@arbash-meinel.com>
	<A8A7C7A0-7DC3-449C-A081-C1557DB9E16A@drivefaster.net>
	<42D70E42.90403@arbash-meinel.com>
	<DF093C91-7FBD-4F46-A2C4-4094650B1534@drivefaster.net>
In-Reply-To: <DF093C91-7FBD-4F46-A2C4-4094650B1534@drivefaster.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigFA1CDD21736CEC465830FA59"

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

Dan Harris wrote:
>
> On Jul 14, 2005, at 7:15 PM, John A Meinel wrote:
>
>>
>>
>> Is the distribution of your rows uneven? Meaning do you have more rows
>> with a later id than an earlier one?
>>
>
> There are definitely some id's that will have many times more than  the
> others.  If I group and count them, the top 10 are fairly  dominant in
> the table.

That usually skews the estimates. Since the estimate is more of an
average (unless the statistics are higher).

>
>>>
>>
>> Hmm.. How to do it permanantly? Well you could always issue "set
>> join_collapse set 1; select * from ...."
>> But obviously that isn't what you prefer. :)
>>
>> I think there are things you can do to make merge join more expensive
>> than a nested loop, but I'm not sure what they are.
>
>
> Maybe someone else has some ideas to encourage this behavior for  future
> work?  Setting it on a per-connection basis is doable, but  would add
> some burden to us in code.

My biggest question is why the planner things the Nested Loop would be
so expensive.
Have you tuned any of the parameters? It seems like something is out of
whack. (cpu_tuple_cost, random_page_cost, etc...)

>
>>
>> What I really don't understand is that the estimates dropped as well.
>> The actual number of estimate rows drops to 3k instead of > 1M.
>> The real question is why does the planner think it will be so  expensive?
>>
>>
>>> select count(*) from k_b join k_r using (incidentid) where k_b.id=107
>>> and k_r.id=94;
>>> count
>>> -------
>>>    373
>>>
>>>
>>
>> Well, this says that they are indeed much more selective.
>> Each one has > 1k rows, but together you end up with only 400.
>>
>
> Is this a bad thing?  Is this not "selective enough" to make it much
> faster?

Yes, being more selective is what makes it faster. But the planner
doesn't seem to notice it properly.

>
> Overall, I'm much happier now after seeing the new plan come about,  if
> I can find a way to make that join_collapse behavior permanent, I  can
> certainly live with these numbers.
>

I'm sure there are pieces to tune, but I've reached my limits of
parameters to tweak :)

> Thanks again for your continued efforts.
>
> -Dan
>

John
=:->

--------------enigFA1CDD21736CEC465830FA59
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

iD8DBQFC1zefJdeBCYSNAAMRAvKrAKDP+f07ZRNOAGveCwnKzT7Tk/oXlQCfZbKM
esJNh4xOtqMT334XcB+blCc=
=/WcL
-----END PGP SIGNATURE-----

--------------enigFA1CDD21736CEC465830FA59--