From: PFC <lists@boutiquenumerique.com>
To: Joe <svn@freedomcircle.net>
To: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Thu, 29 Sep 2005 12:44:55 +0200
Message-ID: <op.sxu1g5bnth1vuj@localhost> (raw)
In-Reply-To: <433B4AA4.9080008@freedomcircle.net>
References: <433B4AA4.9080008@freedomcircle.net>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, svn@freedomcircle.net
Subject: Re: Comparative performance
In-Reply-To: <op.sxu1g5bnth1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox