Why SELECT COUNT(*) takes so long?

[Matthias Apitz <guru@xxxxxxxxxxx>]

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