From: Joe <svn@freedomcircle.net>
To: PFC <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Thu, 29 Sep 2005 08:37:07 -0400
Message-ID: <433BDFF3.3090402@freedomcircle.net> (raw)
In-Reply-To: <op.sxu1g5bnth1vuj@localhost>
References: <433B4AA4.9080008@freedomcircle.net>
<op.sxu1g5bnth1vuj@localhost>
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
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: svn@freedomcircle.net, lists@boutiquenumerique.com
Subject: Re: Comparative performance
In-Reply-To: <433BDFF3.3090402@freedomcircle.net>
* 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