Re: MySQL vs. PostgreSQL

I did notice a considerable difference in time, with a
enquete-result-page (in PHP) using this query:
SELECT
answers.answertext,
answers.answerid,
count(answered_question.answerid) AS answercount
FROM
answers
LEFT JOIN answered_question ON answers.answerid =
answered_question.answerid
WHERE
answers.questionid = '{$this->questionid}'
GROUP BY answers.answerid
ORDER BY answers.answerorder

Which is, I presume, a quite common query...
It runs on exactly the same dataset with exactly the same indices and
table layouts (as far as possible) on exactly the same machine, with
both postgres and mysql optimised 'a bit'.
The query would take 29.45, 55.22 and 17.74 ms on postgresql (for
different questions) takes on mysql respectively: 36.74, 81.14 and 26.37 ms.

Resulting in some simple stats at the end of the page creation:
Database stats Time (s)
Total Execution time: 2.212 s
Total Query time: 2.130 s
Total Number of Queries: 230
Average Time per Query: 0.009 s

vs

Database stats Time (s)
Total Execution time: 3.061 s
Total Query time: 3.002 s
Total Number of Queries: 230
Average Time per Query: 0.013 s

About 150 of these 230 queries are the one showed above. The rest are
simple selects which run 'much' faster on mysql.
The only 'hack' for postgresql I had to make was to set the
ENABLE_MERGEJOIN to OFF, otherwise in some cases it would use the
mergejoin rather than the nested loops on the index scans of both
answers and answered_question, taking over 300ms in stead of 60ms... (a
optimiser problem perhaps?).

Anyway, this shows you that it is not even necessary to have huge
databases, heavy loads or very complicated queries to find postgresql
performing better than mysql.

Regards,

Arjen

Curt Sampson wrote:
> On Fri, 12 Jul 2002, Elaine Lindelef wrote:
> Well, yeah, but it's really time to kill, properly, this idea that
> postgres is always slower than mysql. I'm reasonably convinced that
> under fairly heavy OLTP loads with some large queries going, MySQL would
> grind to a halt at loads much less than postgres can handle.