Re: Drupal and PostgreSQL - performance issues?

On 2008-10-14 23:57, Mikkel Hogh wrote:

> one is the dreaded "SELECT COUNT(pid) FROM
> url_alias" which takes PostgreSQL a whopping 70.65ms out of the
> 115.74ms total for 87 queries.

This is stupid.

The Drupal code looks like this:

// Use $count to avoid looking up paths in subsequent calls
// if there simply are no aliases
if (!isset($count)) {
$count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
}
/* ... */
if ($count > 0 /* */) {
/* one simple query */
}

It is doing count(*) type query (which requires a full table scan in
Postgres) to avoid one simple, indexable query, which is also often
cached. It has to be slower in any database, but it is much, much slower
in Postgres.

Try attached patch for drupal-5.11, and rerun your benchmarks.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh