Re: Joel's Performance Issues WAS : Opteron vs Xeon

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(at)sysexperts(dot)com