Re: Any better plan for this query?..

On 5/18/09, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Mon, 2009-05-18 at 20:00 +0200, Dimitri wrote:
>
>> >From my point of view it needs first to understand where the time is
>> wasted on a single query (even when the statement is prepared it runs
>> still slower comparing to MySQL).
>
> There is still a significant number of things to say about these numbers
> and much tuning still to do, so I'm still confident of improving those
> numbers if we needed to.
>
> 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 )

> distribution of values is clearly non-linear; while Postgres picks a
> strange plan for that particular value, I would guess there are also
> values for which the MySQL plan is sub-optimal. Depending upon the
> distribution of selected data we might see the results go either way.
>
> What I find worrying is your result of a scalability wall for hash
> joins. Is that a repeatable issue?

I think yes (but of course I did not try to replay it several times)

Rgds,
-Dimitri

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