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 D8A5754075 for ; Wed, 27 Apr 2005 18:01:57 -0300 (ADT) 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 66540-05 for ; Wed, 27 Apr 2005 21:01:50 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id C3C4853893 for ; Wed, 27 Apr 2005 18:01:49 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id AB34215553; Wed, 27 Apr 2005 16:01:50 -0500 (CDT) Date: Wed, 27 Apr 2005 16:01:50 -0500 From: "Jim C. Nasby" To: Kevin Brown , 'Postgresql Performance' Subject: Re: Joel's Performance Issues WAS : Opteron vs Xeon Message-ID: <20050427210150.GB58835@decibel.org> References: <4266F7B3.7050004@arbash-meinel.com> <20050423020151.GW58835@decibel.org> <20050423063044.GI19518@filer> <200504231127.42389.josh@agliodbs.com> <20050427024652.GA6839@filer> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050427024652.GA6839@filer> X-Operating-System: FreeBSD 4.11-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.8i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/749 X-Sequence-Number: 12146 BTW, http://stats.distributed.net/~decibel/base.log is a test I ran; select count(*) was ~6x faster than explain analyze select *. On Tue, Apr 26, 2005 at 07:46:52PM -0700, Kevin Brown wrote: > Josh Berkus wrote: > > Jim, Kevin, > > > > > > Hrm... I was about to suggest that for timing just the query (and not > > > > output/data transfer time) using explain analyze, but then I remembered > > > > that explain analyze can incur some non-trivial overhead with the timing > > > > calls. Is there a way to run the query but have psql ignore the output? > > > > If so, you could use \timing. > > > > > > Would timing "SELECT COUNT(*) FROM (query)" work? > > > > Just \timing would work fine; PostgreSQL doesn't return anything until it has > > the whole result set. > > Hmm...does \timing show the amount of elapsed time between query start > and the first results handed to it by the database (even if the > database itself has prepared the entire result set for transmission by > that time), or between query start and the last result handed to it by > the database? > > Because if it's the latter, then things like server<->client network > bandwidth are going to affect the results that \timing shows, and it > won't necessarily give you a good indicator of how well the database > backend is performing. I would expect that timing SELECT COUNT(*) > FROM (query) would give you an idea of how the backend is performing, > because the amount of result set data that has to go over the wire is > trivial. > > Each is, of course, useful in its own right, and you want to be able > to measure both (so, for instance, you can get an idea of just how > much your network affects the overall performance of your queries). > > > > That's why MSSQL vs. PostgreSQL timing comparisons are > > deceptive unless you're careful: MSSQL returns the results on block at a > > time, and reports execution time as the time required to return the *first* > > block, as opposed to Postgres which reports the time required to return the > > whole dataset. > > Interesting. I had no idea MSSQL did that, but I can't exactly say > I'm surprised. :-) > > > -- > Kevin Brown kevin@sysexperts.com > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"