From: Dan Harris <fbsd@drivefaster.net>
To: pgsql-performance@postgresql.org
Subject: Re: slow joining very large table to smaller ones
Date: Thu, 14 Jul 2005 21:49:50 -0600
Message-ID: <DF093C91-7FBD-4F46-A2C4-4094650B1534@drivefaster.net> (raw)
In-Reply-To: <42D70E42.90403@arbash-meinel.com>
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>
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.
>>
>
> 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.
>
> 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?
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.
Thanks again for your continued efforts.
-Dan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: fbsd@drivefaster.net
Subject: Re: slow joining very large table to smaller ones
In-Reply-To: <DF093C91-7FBD-4F46-A2C4-4094650B1534@drivefaster.net>
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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox