From: | Matthias Apitz <guru(at)unixarea(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Why SELECT COUNT(*) takes so long? |
Date: | 2020-09-14 06:09:44 |
Message-ID: | 20200914060944.GA2050@r314251-amd64 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)unixarea(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2020-09-14 06:11:31 | Re: Why SELECT COUNT(*) takes so long? |
Previous Message | Mike Noordermeer | 2020-09-14 05:53:08 | Re: Dirty buffers with suppress_redundant_updates_trigger |