Performance of count(*)

От: Andreas Tille
Тема: Performance of count(*)
Дата: ,
Msg-id: Pine.LNX.4.62.0703221149210.13747@wr-linux02
(см: обсуждение, исходный текст)
Ответы: Re: Performance of count(*)  (Andreas Kostyrka)
Re: Performance of count(*)  (Albert Cervera Areny)
Re: Performance of count(*)  ()
Re: Performance of count(*)  ("Merlin Moncure")
Список: pgsql-performance

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

Performance of count(*)  (Andreas Tille, )
 Re: Performance of count(*)  (Andreas Kostyrka, )
  Re: Performance of count(*)  (Andreas Tille, )
   Re: Performance of count(*)  (Andreas Kostyrka, )
    Re: Performance of count(*)  (Michael Fuhr, )
    Re: Performance of count(*)  (Carlos Moreno, )
   Re: Performance of count(*)  ("Luke Lonergan", )
 Re: Performance of count(*)  (Albert Cervera Areny, )
  Re: Performance of count(*)  (, )
   Re: Performance of count(*)  (Bill Moran, )
   Re: Performance of count(*)  (Mario Weilguni, )
   Re: Performance of count(*)  (Michael Stone, )
    Re: Performance of count(*)  (, )
    Re: Performance of count(*)  ("Craig A. James", )
     Re: Performance of count(*)  (Tino Wildenhain, )
      Re: Performance of count(*)  ("Craig A. James", )
       Re: Performance of count(*)  (Tino Wildenhain, )
        Re: Performance of count(*)  (Michael Stone, )
         Re: Performance of count(*)  ("Merlin Moncure", )
          Re: Performance of count(*)  (Michael Stone, )
          Re: Performance of count(*)  (Tino Wildenhain, )
          Re: Performance of count(*)  (Michael Stone, )
          Re: Performance of count(*)  (Michael Stone, )
         Re: Performance of count(*)  (Tino Wildenhain, )
          Re: Performance of count(*)  (Michael Stone, )
           Re: Performance of count(*)  (Tino Wildenhain, )
        Re: Performance of count(*)  ("Craig A. James", )
         Re: Performance of count(*)  (Tino Wildenhain, )
       Re: Performance of count(*)  (Steve Atkins, )
        Re: Performance of count(*)  ("Craig A. James", )
         Re: Performance of count(*)  (Tom Lane, )
          Re: Performance of count(*)  ("Craig A. James", )
           Re: Performance of count(*)  (Tom Lane, )
        Re: Performance of count(*)  (Guido Neitzer, )
         Re: Performance of count(*)  (Steve Atkins, )
     Re: Performance of count(*)  (Brian Hurt, )
      Re: Performance of count(*)  ("Craig A. James", )
 Re: Performance of count(*)  (, )
 Re: Performance of count(*)  ("Merlin Moncure", )
  Re: Performance of count(*)  ("Jonah H. Harris", )
   Re: Performance of count(*)  (Mario Weilguni, )
    Re: Performance of count(*)  (Andreas Kostyrka, )
     Re: Performance of count(*)  (Mario Weilguni, )
  Re: Performance of count(*)  (Michael Stone, )

Hi,

I just try to find out why a simple count(*) might last that long.
At first I tried explain, which rather quickly knows how many rows
to check, but the final count is two orders of magnitude slower.

My MS_SQL server using colleague can't believe that.

$ psql InfluenzaWeb -c 'explain SELECT count(*) from agiraw ;'
                               QUERY PLAN
-----------------------------------------------------------------------
  Aggregate  (cost=196969.77..196969.77 rows=1 width=0)
    ->  Seq Scan on agiraw  (cost=0.00..185197.41 rows=4708941 width=0)
(2 rows)

real    0m0.066s
user    0m0.024s
sys     0m0.008s

$ psql InfluenzaWeb -c 'SELECT count(*) from agiraw ;'
   count
---------
  4708941
(1 row)

real    0m4.474s
user    0m0.036s
sys     0m0.004s


Any explanation?

Kind regards

          Andreas.

--
http://fam-tille.de


В списке pgsql-performance по дате сообщения:

От: David Brain
Дата:
Сообщение: Re: Potential memory usage issue
От: "Craig A. James"
Дата:
Сообщение: Re: Performance of count(*)