On 5/19/09, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Tue, 2009-05-19 at 00:33 +0200, Dimitri wrote:
>> >
>> > In particular, running the tests repeatedly using
>> > H.REF_OBJECT = '0000000001'
>> > rather than varying the value seems likely to benefit MySQL. The
>>
>> let me repeat again - the reference is *random*,
>> the '0000000001' value I've used just to show a query execution
>> plan.
>>
>> also, what is important - the random ID is chosen in way that no one
>> user use the same to avoid deadlocks previously seen with PostgreSQL
>> (see the "Deadlock mystery" note 2 years ago
>> http://dimitrik.free.fr/db_STRESS_BMK_Part1.html#note_4355 )
>
> OK, didn't pick up on that.
>
> (Like Tom, I was thinking query cache)
>
> Can you comment on the distribution of values for that column? If you
> are picking randomly, this implies distribution is uniform and so I am
> surprised we are mis-estimating the selectivity.
yes, the distribution of reference values is uniform between
'0000000001' to '0010000000' (10M), only one OBJECT row by one
reference, and only 20 rows with the same reference in HISTORY table.
>
>> I think yes (but of course I did not try to replay it several times)
>
> If you could that would be appreciated. We don't want to go chasing
> after something that is not repeatable.
I'll retry and let you know.
Rgds,
-Dimitri