Why is PostgreSQL so slow on Windows?

Question

We had an applicationg running using MySql. We found MySql was not suitable for our app after we found that it didnt support some of the GIS capability that PostGIS has (note: mysql only supports minimum-bounding rectangle GIS search).

So we changed our DB to PostgreSQL. We then found out that Postgresql 8.2 running on Windows is so much slower compared to Mysql 5.1. By slower, I mean at roughly 4-5 times slower.

Why is this? Is there something in the configuration that we need to change?

I found some comments from other websites such as this:

UPDATE: We found that the cause of the slowness is due to the BLOB that we are inserting into the DB. We need to be able to insert BLOB at a sustained rate of 10-15 MB/s. We are using libpq's lo_read and lo_write for each BLOB we are inserting/reading. Is that the best way? Has anyone used Pgsql for inserting large BLOB at a high rate before?

EDIT: I heard that PgSql just recently got ported to Windows. Could this be one of the reasons?

Answer 1

Have you run VACUUM ANALYZE on your database ?

Answer 2

Have you generated statistics to help the optimizer?

Answer 3

While the Windows port of PostgreSQL is relatively recent, my understanding is that it performs about as well as the other versions. But it's definitely a port; almost all developers work primarily or exclusively on Unix/Linux/BSD.

You really shouldn't be running 8.2 on Windows. In my opinion, 8.3 was the first Windows release that was truly production-ready; 8.4 is better yet. 8.2 is rather out of date anyway, and you'll reap several benefits if you can manage to upgrade.

Another thing to consider is tuning. PostgreSQL requires more tuning than MySQL to achieve optimal performance. You may want to consider posting to one of the mailing lists for help with more than basic tweaking.

Answer 4

PostgreSQL is already slower than MySQL up to a certain point (it is actually faster when you have a ridiculously large database). Just FYI, this isn't causing your problem but keep that in mind.

Answer 5

There are cases where PostgreSQL on Windows pays an additional overhead compared to other solutions, due to tradeoffs made when we ported it.

For example, PostgreSQL uses a process per connection, MySQL uses a thread. On Unix, this is usually not a noticeable performance difference, but on Windows creating new processes is very expensive (due to the lack of the fork() system call). For this reason, using persistent connections or a connection pooler is much more important on Windows when using PostgreSQL.

Another issue I've seen is that early PostgreSQL on Windows will by default make sure that it's writes are going through the write cache - even if it's battery backed. AFAIK, MySQL does not do this, and it will greatly affect write performance. Now, this is actually required if you have a non-safe hardware, such as a cheap drive. But if you have a battery-backed write cache, you want to change this to regular fsync. Modern versions of PostgreSQL (certainly 8.3) will default to open_datasync instead, which should remove this difference.

You also mention nothing about how you have tuned the configuration of the database. By default, the configuration file shipped with PostgreSQL is very conservative. If you haven't changed anything there, you definitely need to take a look at it. There is some tuning advice available on the PostgreSQL wiki.

To give any more details, you will have to provide a lot more details about exactly what runs slow, and how you have tuned your database. I'd suggest an email to the pgsql-general mailinglist.