Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BDFE264FE8D for ; Thu, 16 Oct 2008 05:47:55 -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 78929-02 for ; Thu, 16 Oct 2008 05:47:46 -0300 (ADT) Received: from mx1.hub.org (mx1.hub.org [200.46.208.106]) by postgresql.org (Postfix) with ESMTP id F300D64FEAF for ; Thu, 16 Oct 2008 05:47:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from postalmail-a5.g.dreamhost.com (sd-green-bigip-66.dreamhost.com [208.97.132.66]) by mx1.hub.org (Postfix) with ESMTP id 9F77CB0743A for ; Thu, 16 Oct 2008 05:47:41 -0300 (ADT) Received: from Barak.fort.loc (0x57372ee2.mrbnqu1.static.dsl.tele.dk [87.55.46.226]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by postalmail-a5.g.dreamhost.com (Postfix) with ESMTP id 51337BE971 for ; Thu, 16 Oct 2008 01:47:38 -0700 (PDT) Resent-To: pgsql-general@postgresql.org From: =?ISO-8859-1?Q?Mikkel_H=F8gh?= To: Tomasz Ostrowski In-Reply-To: <48F6EE94.8010904@batory.org.pl> Resent-From: =?ISO-8859-1?Q?Mikkel_H=F8gh?= Subject: Re: Drupal and PostgreSQL - performance issues? References: <48F4F053.1060201@hagander.net> <7438A981-4B6A-4E5A-A1E0-50432CDEFB00@hoegh.org> <48F6EE94.8010904@batory.org.pl> Message-Id: <4BE94DFB-8917-4175-9083-7DEABC30BD7F@hoegh.org> Content-Type: multipart/signed; boundary=Apple-Mail-11-1026516302; micalg=sha1; protocol="application/pkcs7-signature" Resent-Date: Thu, 16 Oct 2008 10:47:32 +0200 Mime-Version: 1.0 (Apple Message framework v929.2) Date: Thu, 16 Oct 2008 10:34:07 +0200 X-Mailer: Apple Mail (2.929.2) Resent-Message-Id: <20081016084738.51337BE971@postalmail-a5.g.dreamhost.com> 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/659 X-Sequence-Number: 138908 --Apple-Mail-11-1026516302 Content-Type: text/plain; charset=UTF-8; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable It's not only to avoid one query, but to avoid one query every time =20 drupal_lookup_path() is called (which is every time the system builds =20= a link, which can be dozens of time on a page). So, I think it's probably a worthwhile tradeoff on MyISAM, because =20 such queries are fast there, and you potentially save a bunch of =20 queries, if you're not using URL aliases. Is there a better way to check if a table contains anything in =20 PostgreSQL? Perhaps just selecting one row? -- Kind regards, Mikkel H=C5=99gh 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 =3D 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 =20 > slower > in Postgres. > > Try attached patch for drupal-5.11, and rerun your benchmarks. > > Regards > Tometzky > --=20 > ...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/=20 > path.inc > --- drupal-5.11.orig/includes/path.inc 2006-12-23 = 23:04:52.000000000 =20 > +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 =3D '') { > // $map keys are Drupal paths and the values are the corresponding =20= > aliases > static $map =3D array(), $no_src =3D array(); > - static $count; > - > - // Use $count to avoid looking up paths in subsequent calls if =20 > there simply are no aliases > - if (!isset($count)) { > - $count =3D db_result(db_query('SELECT COUNT(pid) FROM =20 > {url_alias}')); > - } > > if ($action =3D=3D 'wipe') { > $map =3D array(); > $no_src =3D array(); > } > - elseif ($count > 0 && $path !=3D '') { > + elseif ($path !=3D '') { > if ($action =3D=3D 'alias') { > if (isset($map[$path])) { > return $map[$path]; > > --=20 > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general --Apple-Mail-11-1026516302 Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIFdjCCBXIw ggRaoAMCAQICBEVzyswwDQYJKoZIhvcNAQEFBQAwMTELMAkGA1UEBhMCREsxDDAKBgNVBAoTA1RE QzEUMBIGA1UEAxMLVERDIE9DRVMgQ0EwHhcNMDgwNDE3MDgzMjQ2WhcNMTAwNDE3MDkwMjQ2WjB8 MQswCQYDVQQGEwJESzEpMCcGA1UEChMgSW5nZW4gb3JnYW5pc2F0b3Jpc2sgdGlsa255dG5pbmcx QjAbBgNVBAMUFE1pa2tlbCBUaG9zdHJ1cCBI+GdoMCMGA1UEBRMcUElEOjkyMDgtMjAwMi0yLTM3 MTk0MDU1NDQyODCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAtI+nfJ78bdYusbm3hxYgYdsN 3qPmIaHtfL3G/KdaZBScdD/ByCFMmoL4iQ4QUV5ZjgEki/LMngP71l5ESsceUfMgU7VhU2O6CJFC VgGAMgrUO+FEuiso97hbL6Ywkh8C7b3YsXukA3mDBMHSINqDlrq0AJ5zzU852eSFED9C5YkCAwEA AaOCAskwggLFMA4GA1UdDwEB/wQEAwID+DArBgNVHRAEJDAigA8yMDA4MDQxNzA4MzI0NlqBDzIw MTAwNDE3MDkwMjQ2WjCCATcGA1UdIASCAS4wggEqMIIBJgYKKoFQgSkBAQEBAzCCARYwLwYIKwYB BQUHAgEWI2h0dHA6Ly93d3cuY2VydGlmaWthdC5kay9yZXBvc2l0b3J5MIHiBggrBgEFBQcCAjCB 1TAKFgNUREMwAwIBARqBxkZvciBhbnZlbmRlbHNlIGFmIGNlcnRpZmlrYXRldCBn5mxkZXIgT0NF UyB2aWxr5XIsIENQUyBvZyBPQ0VTIENQLCBkZXIga2FuIGhlbnRlcyBmcmEgd3d3LmNlcnRpZmlr YXQuZGsvcmVwb3NpdG9yeS4gQmVt5nJrLCBhdCBUREMgZWZ0ZXIgdmlsa+VyZW5lIGhhciBldCBi ZWdy5m5zZXQgYW5zdmFyIGlmdC4gcHJvZmVzc2lvbmVsbGUgcGFydGVyLjBBBggrBgEFBQcBAQQ1 MDMwMQYIKwYBBQUHMAGGJWh0dHA6Ly9vY3NwLmNlcnRpZmlrYXQuZGsvb2NzcC9zdGF0dXMwGwYD VR0RBBQwEoEQbWlra2VsQGhvZWdoLm9yZzCBhAYDVR0fBH0wezBLoEmgR6RFMEMxCzAJBgNVBAYT AkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMTC1REQyBPQ0VTIENBMRAwDgYDVQQDEwdDUkwyODU4 MCygKqAohiZodHRwOi8vY3JsLm9jZXMuY2VydGlmaWthdC5kay9vY2VzLmNybDAfBgNVHSMEGDAW gBRgtYXsVmR+EhknZx1QFUtzrjv5EjAdBgNVHQ4EFgQU2yH452SE9z/FWNB9LEdD4NpJerYwCQYD VR0TBAIwADAZBgkqhkiG9n0HQQAEDDAKGwRWNy4xAwIDqDANBgkqhkiG9w0BAQUFAAOCAQEAfPFl /JwTWzAB9WGsUzg7h5y2FGcNTvr7Zy4f/E7z+ux4Ysk5FdZv7rEsbCRPPyCWV2+YqhhKbseNI7wC cFW0fOHDIGb50vkE4GALn4D3WDTTwl7fIVY7Vvt2to2TVI/K/Z3gjdKDuVYl54d2GvxLMDZ2VnKB rT/QqMJsS5pSHmQt2W7M4OAlKFFJ/Z0//qc6RM2U3IBBhDVR22mrS7tcqt+jH9ID96UqAadaVzsc nljlBdeHkh1lml9d6urUgfIvLKmoJcX9W9U2C6etgvp2ZR569/v9xG7M2UJktIVN2D0x9kBZoXsW NX/ASsL4/YxaLKEHRGBBcwOr87s3Vj55lzGCAdUwggHRAgEBMDkwMTELMAkGA1UEBhMCREsxDDAK BgNVBAoTA1REQzEUMBIGA1UEAxMLVERDIE9DRVMgQ0ECBEVzyswwCQYFKw4DAhoFAKCB8zAYBgkq hkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wODEwMTYwODQ3MzJaMCMGCSqG SIb3DQEJBDEWBBTYIwCeWqHD09scUKPk+O6dwib7WTBIBgkrBgEEAYI3EAQxOzA5MDExCzAJBgNV BAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMTC1REQyBPQ0VTIENBAgRFc8rMMEoGCyqGSIb3 DQEJEAILMTugOTAxMQswCQYDVQQGEwJESzEMMAoGA1UEChMDVERDMRQwEgYDVQQDEwtUREMgT0NF UyBDQQIERXPKzDANBgkqhkiG9w0BAQEFAASBgC8eAzwrGsrpl8E8EFrGOt59I52f6kIN7d5xxzHw 4023KSWrO7ChbvEKO4qwNRVEqbqolMyRG7PCZlf2fsi8dfUki0DppiNvmetpL+de/hnU78FhqsGH 065rSYchNwIUpu1GpB04RprBLGCDihogdz2vpcDXQA/jOjCwGaYR9yStAAAAAAAA --Apple-Mail-11-1026516302--