Qingqing Zhou wrote:
"Arjen van der Meijden" <[EMAIL PROTECTED]> wrote
Some sort of web query behavior is quite optimized in MySQL. For example,
the query below is runing very fast due to the query result cache
implementation in MySQL.
Loop N times
SELECT * FROM A WHERE i = 1;
End loop.
Yeah, I know. But our queries get random parameters though for
identifiers and the like, so its not just a few queries getting executed
a lot of times, there are. In a run for which I just logged all queries,
almost 42k distinct queries executed from 128k in total (it may actually
be more random than real life).
Besides that, they are not so extremely simple queries as your example.
Most join at least two tables, while the rest often joins three to five.
But I agree, MySQL has a big advantage with its query result cache. That
makes the current performance of postgresql even more impressive in this
situation, since the query cache of the 4.1.x run was enabled as well.
IMHO, without knowing the exact queries you sent, these logs are not very
useful :-(. I would suggest you compare the queries in pair and then post
their dtrace/timing results here (just like the previous Firebird vs.
PostgreSQL comparison did).
Well, I'm bound to some privacy and copyright laws, but I'll see if I
can show some example plans of at least the top few queries later today
(the top two is resp 27% and 21% of the total time).
But those top queries aren't the only ones run during the benchmarks or
in the production environment, nor are they run exclusively at any given
time. So the overall load-picture should be usefull too, shouldn't it?
Best regards,
Arjen
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
