Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 53E666327BE for ; Mon, 26 Jul 2010 06:25:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 71960-08 for ; Mon, 26 Jul 2010 09:25:24 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id A5E4762E706 for ; Mon, 26 Jul 2010 06:25:23 -0300 (ADT) Received: by bwz1 with SMTP id 1so2658707bwz.19 for ; Mon, 26 Jul 2010 02:25:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=Y/6Omrn/PG88wv5PlXYSQsx9RhcYND5njAfU3jRX+cs=; b=upDay4VopxZx+EcwRzroeIztZzn1YEz6dPJzUkuz30nxloAVzXFsI9eA/vtI2iae+F uP3HbJNy7pB2ud2xkH+iZqd1oiHrkzNvVlyKHF1C08NgALxoSPNpw13hknszCYKls4jy jW8Drvp/HGlNqm5ozaXv8tTC9hTIHLillcMrw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; b=Jwz0Jz7yG3/mEkQLGXS1yolpYDfpOLA1taNqEVfFJw994nQplXp8NBSAC5HszGrsTK tbI6P9Wkvlagtj8n6TAvfcLLKAmlYr+vT8Ut35BwYUMZw/RFasp7pf3wVxnSX/jvIrNB j8Depew8ZHzH6hMzSh7nw7qUAk/MOU0ggp/Ls= Received: by 10.204.163.69 with SMTP id z5mr5075060bkx.167.1280136321328; Mon, 26 Jul 2010 02:25:21 -0700 (PDT) Received: from [10.1.1.57] (skuns.zoral.com.ua [91.193.166.194]) by mx.google.com with ESMTPS id g11sm2706786bkw.22.2010.07.26.02.25.19 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 26 Jul 2010 02:25:20 -0700 (PDT) Message-ID: <4C4D547E.50905@gmail.com> Date: Mon, 26 Jul 2010 12:25:18 +0300 From: Vitalii Tymchyshyn User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.10) Gecko/20100528 Thunderbird/3.0.5 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ... References: <4C4D5220.9010101@postnewspapers.com.au> In-Reply-To: <4C4D5220.9010101@postnewspapers.com.au> 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: 201007/374 X-Sequence-Number: 39763 26.07.10 12:15, Craig Ringer написав(ла): > On 26/07/10 16:35, Piotr Gasidło wrote: > >> Hello, >> >> I've found strange problem in my database (8.4.4, but also 9.0beta3, >> default postgresql.conf, shared_buffers raised to 256MB). >> >> EXPLAIN ANALYZE SELECT ... >> Total runtime: 4.782 ms >> Time: 25,970 ms >> >> SELECT ... >> ... >> (21 rows) >> >> Time: 23,042 ms >> >> Test done in psql connected by socket to server (same host, using >> \timing to get runtime). >> >> Does big difference in "Total runtime" and "Time" is normal? >> > Given that EXPLAIN ANALYZE doesn't transfer large rowsets to the client, > it can't really be time taken to transfer the data, which is the usual > difference between 'explain analyze' timings and psql client-side timings. > > Given that, I'm wondering if the difference in this case is planning > time. I can't really imagine the query planner taking 20 seconds (!!) to > run, though, no matter how horrifyingly complicated the query and table > structure were, unless there was something going wrong. > Actually it's 20ms, so I suspect your point about planning time is correct. Piotr: You can try preparing your statement and then analyzing execute time to check if this is planning time. Best regards, Vitalii Tymchyshyn