Re: Drupal and PostgreSQL - performance issues?

[Mikkel Høgh <mikkel@xxxxxxxxx>]

It's not only to avoid one query, but to avoid one query every time  
drupal_lookup_path() is called (which is every time the system builds  
a link, which can be dozens of time on a page).

So, I think it's probably a worthwhile tradeoff on MyISAM, because  
such queries are fast there, and you potentially save a bunch of  
queries, if you're not using URL aliases.

Is there a better way to check if a table contains anything in  
PostgreSQL? Perhaps just selecting one row?
--
Kind regards,

Mikkel Hřgh <mikkel@xxxxxxxxx>

On 16/10/2008, at 09.34, Tomasz Ostrowski wrote:

> 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];
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

<<attachment: smime.p7s>>