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

От: Kevin Brown
Тема: Re: Joel's Performance Issues WAS : Opteron vs Xeon
Дата: ,
Msg-id: 20050427024652.GA6839@filer
(см: обсуждение, исходный текст)
Ответ на: Re: Joel's Performance Issues WAS : Opteron vs Xeon  (Josh Berkus)
Ответы: Re: Joel's Performance Issues WAS : Opteron vs Xeon  ("Jim C. Nasby")
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Simon Riggs
Дата:
Сообщение: Re: [HACKERS] Bad n_distinct estimation; hacks suggested?
От: Yann Michel
Дата:
Сообщение: Re: What needs to be done for real Partitioning?