Skip site navigation (1) Skip section navigation (2)

Re: Comparative performance

From: PFC <lists(at)boutiquenumerique(dot)com>
To: Joe <svn(at)freedomcircle(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Comparative performance
Date: 2005-09-29 10:44:55
Message-ID: op.sxu1g5bnth1vuj@localhost (view raw or flat)
Thread:
Lists: pgsql-performance
> 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.

In response to

Responses

pgsql-performance by date

Next:From: Pailloncy Jean-GerardDate: 2005-09-29 11:11:45
Subject: Re: [PERFORM] A Better External Sort?
Previous:From: Ron PeacetreeDate: 2005-09-29 07:42:54
Subject: Re: Sequential I/O Cost (was Re: A Better External Sort?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group