Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 913039FA19B; Fri, 15 Dec 2006 06:28:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 75376-03; Fri, 15 Dec 2006 06:28:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id BF25C9FA0EF; Fri, 15 Dec 2006 06:28:51 -0400 (AST) thread-index: AccgM9LZBnkxhql2T+2ZAQWnGmnnag== Received: from [192.168.0.24] ([62.232.55.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 15 Dec 2006 05:28:55 -0500 Subject: Re: EXPLAIN ANALYZE on 8.2 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 Content-Class: urn:content-classes:message Importance: normal From: "Simon Riggs" To: "Tom Lane" Cc: "Kelly Burkhart" , "Evgeny Gridasov" , , In-Reply-To: <29450.1166141131@sss.pgh.pa.us> References: <20061214163230.d46f0138.eugrid@fpm.kubsu.ru> <21146.1166112702@sss.pgh.pa.us> <20061214192920.c75385d6.eugrid@fpm.kubsu.ru> <21516.1166114475@sss.pgh.pa.us> <29450.1166141131@sss.pgh.pa.us> Content-Type: text/plain; charset="iso-8859-1" Date: Fri, 15 Dec 2006 10:28:08 +0000 Message-ID: <1166178488.3882.56.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 15 Dec 2006 10:28:55.0359 (UTC) FILETIME=[D2B114F0:01C72033] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/399 X-Sequence-Number: 22455 On Thu, 2006-12-14 at 19:05 -0500, Tom Lane wrote: > "Kelly Burkhart" writes: > > I hope this isn't a "crummy mainboard" but I can't seem to affect > > things by changing clock source (kernel 2.6.16 SLES10). I tried > > kernel command option clock=XXX where XXX in > > (cyclone,hpet,pmtmr,tsc,pit), no option was significantly better than > > the default. > > I believe that on machines where gettimeofday is really nice and fast, > it doesn't require entry to the kernel at all; there's some hack that > makes the clock readable from userspace. (Obviously a switch to kernel > mode would set you back a lot of the cycles involved here.) So it's not > so much the kernel that you need to teach as glibc. How you do that is > beyond my expertise, but maybe that will help you google for the right > thing ... Until we work out a better solution we can fix this in two ways: 1. EXPLAIN ANALYZE [ [ WITH | WITHOUT ] TIME STATISTICS ] ... 2. enable_analyze_timer = off | on (default) (USERSET) A performance drop of 4x-10x is simply unacceptable when trying to tune queries where the current untuned time is already too high. Tying down production servers for hours on end when we know for certain all they are doing is calling gettimeofday millions of times is not good. This quickly leads to the view from objective people that PostgreSQL doesn't have a great optimizer, whatever we say in its defence. I don't want to leave this alone, but I don't want to spend a month fixing it either. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com