Re: Comparative 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