Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1We6iC-0002Mw-Ah for pgsql-admin@arkaria.postgresql.org; Sat, 26 Apr 2014 17:48:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1We6iB-0006S8-EB for pgsql-admin@arkaria.postgresql.org; Sat, 26 Apr 2014 17:48:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1We6iA-0006S1-9t for pgsql-admin@postgresql.org; Sat, 26 Apr 2014 17:48:26 +0000 Received: from mailsrv14.zmi.at ([2001:1b48:202:affe::36]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1We6i6-0007pq-NI for pgsql-admin@postgresql.org; Sat, 26 Apr 2014 17:48:25 +0000 Received: from mailsrv.i.zmi.at (unknown [10.127.27.3]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (Client CN "mailsrv2.i.zmi.at", Issuer "power4u.zmi.at" (not verified)) by mailsrv14.zmi.at (Postfix) with ESMTPSA id 607D831A for ; Sat, 26 Apr 2014 19:48:20 +0200 (CEST) Received: from saturn.i.zmi.at (saturn.i.zmi.at [10.72.27.2]) by mailsrv.i.zmi.at (Postfix) with ESMTPA id 342A1100B794 for ; Sat, 26 Apr 2014 19:48:20 +0200 (CEST) Message-ID: <535BF163.9070401@is.it-management.at> Date: Sat, 26 Apr 2014 19:48:19 +0200 From: Michael Monnerie User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: EXPLAIN time difference in real References: <535BACAC.90600@is.it-management.at> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060901020009060201020502" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------060901020009060201020502 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Am 26.04.2014 16:17, schrieb Jov: > Maybe your data or index is read from disk for some write heavy load > time,your sql get 1300ms.when you explain analyze the sql,the data and > index already in cached in the memory. > > you can verify this by restart the server and run the sql from a fresh > start postgresql. > Tried that: EXPLAIN ANALYZE.... Total runtime: 280.537 ms EXPLAIN:LOG: duration: 1501.807 ms statement: EXPLAIN ANALYZE VERBOSE SELECT So again 1500ms versus 280ms. Why? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc, Tel: +43 660 415 6531 Protéger.at Internet Services Austria Web: http://protéger.at und http://proteger.at Facebook https://facebook.com/protegerat Mitglied im it-management Netzwerk http://it-management.at --------------060901020009060201020502 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
Am 26.04.2014 16:17, schrieb Jov:
Maybe your data or index is read from disk for some write heavy load time,your sql get 1300ms.when you explain analyze the sql,the data and index already in  cached in the memory.

you can verify this by restart the server and run the sql from a fresh start postgresql.  


Tried that:
EXPLAIN ANALYZE....
Total runtime: 280.537 ms

EXPLAIN:LOG:  duration: 1501.807 ms  statement: EXPLAIN ANALYZE VERBOSE SELECT

So again 1500ms versus 280ms. Why?

-- 
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc, Tel: +43 660 415 6531

Protéger.at Internet Services Austria
Web: http://protéger.at und http://proteger.at 
Facebook https://facebook.com/protegerat
Mitglied im it-management Netzwerk http://it-management.at
--------------060901020009060201020502--