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

From: Kevin Brown
Subject: Re: Joel's Performance Issues WAS : Opteron vs Xeon
Date: ,
Msg-id: 20050427024652.GA6839@filer
(view: Whole thread, Raw)
In response to: Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Josh Berkus)
Responses: Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Jim C. Nasby")
List: pgsql-performance

Tree view

Re: Opteron vs Xeon (Was: What to do with 6 disks?)  ("Anjan Dave", )
 Re: Opteron vs Xeon (Was: What to do with 6 disks?)  ("Joel Fradkin", )
  Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Josh Berkus, )
   Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Joel Fradkin", )
    Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Josh Berkus, )
     Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Vivek Khera, )
    Re: Joel's Performance Issues WAS : Opteron vs Xeon  (John A Meinel, )
     Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Joel Fradkin", )
      Re: Joel's Performance Issues WAS : Opteron vs Xeon  (John A Meinel, )
       Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Tom Lane, )
        Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Joel Fradkin", )
       Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Joel Fradkin", )
        Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Jim C. Nasby", )
         Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Kevin Brown, )
          Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Josh Berkus, )
           Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Kevin Brown, )
            Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Jim C. Nasby", )
      Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Josh Berkus, )
      Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Andreas Pflug, )

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                          


pgsql-performance by date:

From: Simon Riggs
Date:
Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested?
From: Yann Michel
Date:
Subject: Re: What needs to be done for real Partitioning?