It appears that PostgreSQL is two to three times slower than MySQL. For example, some pages that have some 30,000 characters (when saved as HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with PostgreSQL. I had read that the former was generally faster than the latter, particularly for simple web applications but I was hoping that Postgres' performance would not be that noticeably slower.

From my experience, the postgres libraries in PHP are a piece of crap, and add a lot of overhead even from small queries. For instance, a simple query like "SELECT * FROM table WHERE primary_key_id=1234" can take the following time, on my laptop, with data in the filesystem cache of course :

EXPLAIN ANALYZE <0.1 ms
python + psycopg 2      0.1 ms (damn fast)
php + mysql             0.3 ms
php + postgres  1-2 ms (damn slow)

So, if your pages are designed in The PHP Way (ie. a large number of small queries), I might suggest using a language with a decent postgres interface (python, among others), or rewriting your bunches of small queries as Stored Procedures or Joins, which will provide large speedups. Doing >50 queries on a page is always a bad idea, but it's tolerable in php-mysql, not in php-postgres.

If it's only one large query, there is a problem, as postgres is usually a lot smarter about query optimization.

If you use the usual mysql techniques (like, storing a page counter in a row in a table, or storing sessions in a table) beware, these are no-nos for postgres, these things should NOT be done with a database anyway, try memcached for instance.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to