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

Re: Comparative performance

From: Joe <svn(at)freedomcircle(dot)net>
To: PFC <lists(at)boutiquenumerique(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Comparative performance
Date: 2005-09-29 12:37:07
Message-ID: 433BDFF3.3090402@freedomcircle.net (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: JoeDate: 2005-09-29 12:44:16
Subject: Re: Comparative performance
Previous:From: Gavin SherryDate: 2005-09-29 12:31:06
Subject: Re: Comparative performance

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