Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B520F65008C for ; Thu, 16 Oct 2008 04:35:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65296-02 for ; Thu, 16 Oct 2008 04:34:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from batyskaf.batory.org.pl (mail.batory.org.pl [62.89.72.199]) by postgresql.org (Postfix) with ESMTP id E8FE365009C for ; Thu, 16 Oct 2008 04:34:53 -0300 (ADT) Received: from pancernik.batory.org.pl (pancernik.batory.lan [192.168.30.210]) (authenticated bits=0) by batyskaf.batory.org.pl (8.13.8/8.13.8) with ESMTP id m9G7YiT6014086 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 16 Oct 2008 09:34:44 +0200 Message-ID: <48F6EE94.8010904@batory.org.pl> Date: Thu, 16 Oct 2008 09:34:44 +0200 From: Tomasz Ostrowski User-Agent: Thunderbird 2.0.0.16 (X11/20080723) MIME-Version: 1.0 To: =?ISO-8859-2?Q?Mikkel_H=3Fgh?= CC: pgsql-general@postgresql.org Subject: Re: Drupal and PostgreSQL - performance issues? References: <48F4F053.1060201@hagander.net> <7438A981-4B6A-4E5A-A1E0-50432CDEFB00@hoegh.org> In-Reply-To: <7438A981-4B6A-4E5A-A1E0-50432CDEFB00@hoegh.org> X-Enigmail-Version: 0.95.7 Content-Type: multipart/mixed; boundary="------------070002060207080101020007" X-Scanned-By: MIMEDefang 2.64 on 192.168.30.2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/658 X-Sequence-Number: 138907 This is a multi-part message in MIME format. --------------070002060207080101020007 Content-Type: text/plain; charset=ISO-8859-2 Content-Transfer-Encoding: 7bit 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 --------------070002060207080101020007 Content-Type: text/x-patch; name="drupal-5.11-url_alias-no-count.patch" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="drupal-5.11-url_alias-no-count.patch" 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]; --------------070002060207080101020007--