From: Kevin Brown <kevin@sysexperts.com>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Joel's Performance Issues WAS : Opteron vs Xeon
Date: Tue, 26 Apr 2005 19:46:52 -0700
Message-ID: <20050427024652.GA6839@filer> (raw)
In-Reply-To: <200504231127.42389.josh@agliodbs.com>
References: <4266F7B3.7050004@arbash-meinel.com>
<20050423020151.GW58835@decibel.org>
<20050423063044.GI19518@filer>
<200504231127.42389.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kevin@sysexperts.com
Subject: Re: Joel's Performance Issues WAS : Opteron vs Xeon
In-Reply-To: <20050427024652.GA6839@filer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox