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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
