X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 24F26329E7B for ; Tue, 12 Oct 2004 01:28:34 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20439-03 for ; Tue, 12 Oct 2004 00:28:29 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id CB908329E65 for ; Tue, 12 Oct 2004 01:28:31 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9C0SSGY025941; Mon, 11 Oct 2004 20:28:28 -0400 (EDT) To: "Chris Hutchinson" Cc: pgsql-performance@postgresql.org Subject: Re: EXPLAIN ANALYZE much slower than running query normally In-reply-to: References: Comments: In-reply-to "Chris Hutchinson" message dated "Tue, 05 Oct 2004 16:49:26 +1000" Date: Mon, 11 Oct 2004 20:28:28 -0400 Message-ID: <25940.1097540908@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/149 X-Sequence-Number: 8625 "Chris Hutchinson" writes: > Running a trivial query in v7.4.2 (installed with fedora core2) using > EXPLAIN ANALYZE is taking considerably longer than just running the query > (2mins vs 6 secs). I was using this query to quickly compare a couple of > systems after installing a faster disk. Turning on EXPLAIN ANALYZE will incur two gettimeofday() kernel calls per row (in this particular plan), which is definitely nontrivial overhead if there's not much I/O going on. I couldn't duplicate your results exactly, but I did see a test case with 2.5 million one-column rows go from <4 seconds to 21 seconds, which makes the cost of a gettimeofday about 3.4 microseconds on my machine (Fedora Core 3, P4 running at something over 1Ghz). When I widened the rows to a couple hundred bytes, the raw runtime went up to 30 seconds and the analyzed time to 50, so the overhead per row is pretty constant, as you'd expect. Some tests with a simple loop around a gettimeofday call yielded a value of 2.16 microsec/gettimeofday, so there's some overhead attributable to the EXPLAIN mechanism as well, but the kernel call is clearly the bulk of it. regards, tom lane