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

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, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

> On Sep 13, 2020, at 23:09, Matthias Apitz <guru@unixarea.de> 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
   xof@thebuild.com