Re: Drupal and PostgreSQL - performance issues? - Mailing list pgsql-general

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
diff -urNP drupal-5.11.orig/includes/path.inc drupal-5.11/includes/path.inc
--- drupal-5.11.orig/includes/path.inc    2006-12-23 23:04:52.000000000 +0100
+++ drupal-5.11/includes/path.inc    2008-10-16 09:26:48.000000000 +0200
@@ -42,18 +42,12 @@
 function drupal_lookup_path($action, $path = '') {
   // $map keys are Drupal paths and the values are the corresponding aliases
   static $map = array(), $no_src = array();
-  static $count;
-
-  // 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 ($action == 'wipe') {
     $map = array();
     $no_src = array();
   }
-  elseif ($count > 0 && $path != '') {
+  elseif ($path != '') {
     if ($action == 'alias') {
       if (isset($map[$path])) {
         return $map[$path];