Why SELECT COUNT(*) takes so long?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Why SELECT COUNT(*) takes so long?

Matthias Apitz

Hello,

I've setup a relatively big database on RH 8.2 with PG 11.4. The table
in question has aroung 38 millions of rows. Why a SELECT COUNT(*) of the
full table takes around 1 minute:

# date ; printf "select count(*) from d01buch ;\n" | /usr/local/sisis-pap/pgsql/bin/psql -Usisis -dsisis ; date
Mo 14. Sep 07:48:36 CEST 2020
  count
----------
 37982555
(1 Zeile)

Mo 14. Sep 07:49:38 CEST 2020

while a SELECT using an indexed row does not take significant time:

# date ; printf "select count(*) from d01buch where d01gsi='F-2014-30663189X' ; \n" | /usr/local/sisis-pap/pgsql/bin/psql -Usisis -dsisis ; date
Mo 14. Sep 07:50:23 CEST 2020
 count
-------
     1
(1 Zeile)

I now such longish SELECT COUNT(*) from Sybase when UPDATE STATISTICS is
not done for the tables. Is there something similar for PostgreSQL?

Thanks

        matthias

--
Matthias Apitz, ✉ [hidden email], http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!


Reply | Threaded
Open this post in threaded view
|

Re: Why SELECT COUNT(*) takes so long?

Christophe Pettus-2


> On Sep 13, 2020, at 23:09, Matthias Apitz <[hidden email]> wrote:
> Why a SELECT COUNT(*) of the
> full table takes around 1 minute:

There's an explanation here:

        https://wiki.postgresql.org/wiki/Slow_Counting

--
-- Christophe Pettus
   [hidden email]