Re: mysql to postgresql, performance questions

On Thu, Mar 18, 2010 at 10:31 AM, Corin <wakathane(at)gmail(dot)com> wrote:
> I'm running quite a large social community website (250k users, 16gb
> database). We are currently preparing a complete relaunch and thinking about
> switching from mysql 5.1.37 innodb to postgresql 8.4.2. The database server
> is a dual dualcore operton 2216 with 12gb ram running on debian amd64.
>
> For a first impression I ran a simple query on our users table (snapshot
> with only ~ 45.000 records). The table has an index on birthday_age
> [integer]. The test executes 10 times the same query and simply discards the
> results. I ran the tests using a php and a ruby script, the results are
> almost the same.
>
> Unluckily mysql seems to be around 3x as fast as postgresql for this simple
> query. There's no swapping, disc reading involved...everything is in ram.
>
> query
> select * from users where birthday_age between 12 and 13 or birthday_age
> between 20 and 22 limit 1000

couple of points:
\timing switch in psql is the best way to get timing results that are
roughly similar to what your application will get, minus the overhead
of your application.

your issue is likely coming from one of three places:
1) connection/ssl/client library issue: maybe you are using ssl in
postgres but not mysql, or some other factor which is outside the
database
2) not apples to apples: postgres schema is missing an index, or
something similar.
3) mysql generated a better plan: mysql has a simpler query
planner/statistics model that can occasionally generate a better plan
or (if you are using myisam) mysql can do tricks which are impractical
or impossible in the mvcc transactional system postgres uses.

so, you have to figure out which of those three things you are looking
at, and then fix it if the query is performance critical.

merlin