Re: Any better plan for this query?..

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.

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

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support