From: Tomasz Ostrowski <tometzky@batory.org.pl>
To: Mikkel H?gh <mikkel@hoegh.org>
Cc: pgsql-general@postgresql.org
Subject: Re: Drupal and PostgreSQL - performance issues?
Date: Thu, 16 Oct 2008 09:34:44 +0200
Message-ID: <48F6EE94.8010904@batory.org.pl> (raw)
In-Reply-To: <7438A981-4B6A-4E5A-A1E0-50432CDEFB00@hoegh.org>
References: <a93e6cbe-0000-4000-9a34-4db3620c619d@mm>
<C6449FCD-198D-4A04-BACB-1BB2F20DD5B1@hoegh.org>
<48F4F053.1060201@hagander.net>
<F933F17A-70BC-4DB0-9491-9EEF0E79ABD9@hoegh.org>
<b42b73150810141422i5ad693a8la3966fa6334c23f7@mail.gmail.com>
<7438A981-4B6A-4E5A-A1E0-50432CDEFB00@hoegh.org>
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
Attachments:drupal-5.11-url_alias-no-count.patch (text/x-patch, 871B) download
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];
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: tometzky@batory.org.pl, mikkel@hoegh.org, pgsql-general@postgresql.org
Subject: Re: Drupal and PostgreSQL - performance issues?
In-Reply-To: <48F6EE94.8010904@batory.org.pl>
* 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