Message-ID: <433BDFF3.3090402@freedomcircle.net>
Date: Thu, 29 Sep 2005 08:37:07 -0400
From: Joe <svn@freedomcircle.net>
Organization: Freedom Circle, LLC
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: PFC <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
References: <433B4AA4.9080008@freedomcircle.net> <op.sxu1g5bnth1vuj@localhost>
In-Reply-To: <op.sxu1g5bnth1vuj@localhost>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

PFC wrote:
>     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)

As a Trac user I was considering moving to Python, so it's good to know that, 
but the rewrite is a longer term project.

>     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.

The pages do use a number of queries to collect all the data for display but 
nowhere near 50.  I'd say it's probably less than a dozen.  As an aside, one of 
my tasks (before the conversion) was to analyze the queries and see where they 
could be tweaked for performance, but with MySQL that was never a top priority.

The schema is fairly simple having two main tables: topic and entry (sort of 
like account and transaction in an accounting scenario).  There are two 
additional tables that perhaps could be merged into the entry table (and that 
would reduce the number of queries) but I do not want to make major changes to 
the schema (and the app) for the PostgreSQL conversion.

Joe