Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 4D6041337B50 for ; Wed, 6 Oct 2010 08:46:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 22749-10 for ; Wed, 6 Oct 2010 11:45:57 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.csc.lt (mail.csc.lt [193.138.191.5]) by mail.postgresql.org (Postfix) with ESMTP id 824E51337B47 for ; Wed, 6 Oct 2010 08:45:55 -0300 (ADT) Received: (qmail 8169 invoked by uid 210); 6 Oct 2010 11:45:55 -0000 Received: from mail.nsoft.lt by mail (envelope-from , uid 201) with qmail-scanner-2.05st (clamdscan: 0.95.3/12089. spamassassin: 3.2.1. perlscan: 2.05st. Clear:RC:1(77.240.249.53):. Processed in 0.015725 secs); 06 Oct 2010 11:45:55 -0000 Received: from mail.nsoft.lt (77.240.249.53) by mail.csc.lt with SMTP; 6 Oct 2010 11:45:55 -0000 Received: from [127.0.0.1] (unknown [77.240.249.53]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (No client certificate requested) by mail.nsoft.lt (Postfix) with ESMTPS id 35B058E06D for ; Wed, 6 Oct 2010 14:45:53 +0300 (EEST) Message-ID: <4CAC6168.20608@nsoft.lt> Date: Wed, 06 Oct 2010 14:45:44 +0300 From: Julius Tuskenis Organization: UAB nSoft User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; lt; rv:1.9.2.9) Gecko/20100915 Lightning/1.0b2 Thunderbird/3.1.4 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: query execution time References: <1e5d6016dca5b8022106577ec316208f@mail.nsoft.lt> <4CAB52A0020000250003657E@gw.wicourts.gov> In-Reply-To: <4CAB52A0020000250003657E@gw.wicourts.gov> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/55 X-Sequence-Number: 35796 2010.10.06 00:30, Kevin Grittner rašė: > There can be a number of reasons. The time to transmit the query > results to the client is one that jumps to mind, if the query > generates a large result set. Parts of what you described might > suggest checkpoint issues, depending on the query mix, the > PostgreSQL version, the hardware and the OS. Version string: PostgreSQL 8.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2) > You haven't given us > enough information to even start to guess which of these or other > issues might be involved. Yes, you are right, but there is not much I can send you, because there are several unrelated queries that act the same way (that is have "jumping" execution times). If you please see https://sites.google.com/site/juliaustestukas/Home I think the picture would explain the problem better then I can... I have put red ellipses around the times that are too different. Maybe you know what is done after the query is executed? As I said the traffic limit is not reached. There must be something else. If there was a hardware issue I guess the EXPLAIN ANALYZE would show the execution time comparable to the observed one... One more thing that may be connected to this - the encoding was changed for DB using instructions on http://bryan-murdock.blogspot.com/2008/11/convert-postgresql-database-from-latin1.html . We converted it to UTF-8 from SQL_ASCII . Manual vacuum was executed after that. The settings for autovacuum were not changed so it is enabled by default on every table. Please, could you provide information what actions does the postgres take after it does actions monitored by EXPLAIN ANALYZE? Could it be libpq issue (pgAdmin uses it)? -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050