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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to