Re: Any better plan for this query?..

Dimitri wrote:
> I'll try to answer all mails at once :-))
>
> - query is running fully in RAM, no I/O, no network, only CPU time
>
> - looping 100 times the same query gives 132ms total time (~1.32ms per
> query), while it's 44ms on InnoDB (~0.44ms per query)

Well, assuming you're happy that PG is tuned reasonably for your machine
and that MySQL's query cache isn't returning the results here it looks
like MySQL is faster for this particular query.

The only obvious place there could be a big gain is with the hashing
algorithm. If you remove the ORDER BY and the query-time doesn't fall by
much then it's the hash phase.

The other thing to try is to alter the query to be a SELECT count(*)
rather than returning rows - that will let you measure the time to
transfer the result rows.

--
Richard Huxton
Archonet Ltd