Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oBKa2-0000ft-65 for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jul 2022 18:25:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oBKa1-0003vs-2o for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jul 2022 18:25:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oBKa0-0003u0-N8 for pgsql-admin@lists.postgresql.org; Tue, 12 Jul 2022 18:25:52 +0000 Received: from mout.perfora.net ([74.208.4.197]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oBKZy-0004C6-3C for pgsql-admin@lists.postgresql.org; Tue, 12 Jul 2022 18:25:51 +0000 Received: from oxuslxaltgw14.schlund.de ([10.72.76.70]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MbwB6-1ntFUq0ipy-00JFdA; Tue, 12 Jul 2022 20:25:46 +0200 Date: Tue, 12 Jul 2022 14:25:45 -0400 (EDT) From: MichaelDBA Vitale To: "Pierson Patricia L (Contractor)" , "gogala.mladen@gmail.com" Cc: "pgsql-admin@lists.postgresql.org" Message-ID: <1037158693.49441.1657650345834@email.ionos.com> In-Reply-To: References: <1B1927BA-F7DE-40E8-8F67-BCC001CD4C6B@gmail.com> <1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com> <1221536526.20277.1657636277921@email.ionos.com> Subject: RE: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Mailer: Open-Xchange Mailer v7.10.5-Rev44 X-Originating-Client: open-xchange-appsuite X-Provags-ID: V03:K1:J9r4VMO7DMl4urezs/vY/vcIrhoUcpvvNtoUhCBGJ/ITDuNSPU4 vQt9A1VQnp9oUmN2tv0eMFx251yS92W7yPNTZm7tjABp7RFepEs5lW3ohgJnq1p0fl6rAuS 6CZek/G+L7qpUZTilLqb8mKXq+ldsNgX7+Dfrfi5hjLxzft1PEqmg4Q77fZnD0BKBo0sVfq SRkkvKW60XoKWrGwe3zNw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:h5yNwpwgtlg=:FwNwOWw3a1pWdzUZP0kmwD UjPplHetQYgOc/SPbqpQNvux0uXnpO2pZoAN+wtTj1rZyMUYGNE9PkNB7ukNhK7bjuccUDDEm 2QAB/4llHBr9jpXoqDHnhlSEYo23QqK0ooQVHRpTv31PtT9xN2X/L06/PseD3oNkXU5/rv3hY u8JHxAYvdbZYT+G6aFQseSyzzOMDOJo1GA53Mo604pCWv8eXYOLgfEgQODnSQ4DmufAT/QJxA +TIkKNVvs2eX8UM1ixske/vkXMz+0gf22NzXvnzbEHzjEedHg3v2T8xdvR3BsYSndR250Kt4w 6mWqqYeWLNk26F5hcOykkSmyg+pVxLQfHoKNyzcv0j9OGgMltNdzHhtXPbqbC45zyb8+h0WeY 2XXavc7HEM9fZ76dJv/oZ/YSFHnlXftS0WX98lNIh5gTyaJMjVmE6nMJm6e9BX3/tKacKBjDp HGwE4bUk0LxXzNzyK2fDU5SjkJ3bb0NaH26gTsNPedMEcYq3M9Gv93LHwKOogxH+OW5gkJo6U n5st9xfqFGbJjhyufyMScOJVxaYBhBQme53DwrgVcw7taLVhmwgHTTlRi8q6noLuslh3GCvNF Gxqhtzs3zU0NUls/dRTMNdPGRC5CJlbXxvg8xl4GTyS6zBlgEcl5BeE37raPhUacPq4JAzIp/ E+qu16873N3AtVGsWfZZYqvzdbVQKU7+m8dUTKsxFvVcL5mUHRg3mUsCvuRlrS+mskifdwNQ3 B+qcuDfaLo2nDcmP List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk =20 =20

That is not true: doing the select on the primary key will still result = in a table scan, not an index scan. The heap always gets accessed for= select counts.

Regards,

Michael Vitale

On 07/12/2022 2:13 PM Pierson Patricia L (Contractor) <patricia.l.pi= erson@irs.gov> wrote:
Hello,

Do a count on the primary key. Will force index= access and you don=E2=80=99t access the entire row which may be very long.=

LIKE : select count(ID) from my_table;

From: Mladen Gogala <gogala= .mladen@gmail.com>
Sent: Tuesday, July 12, 2022 11:= 58 AM
To: MichaelDBA Vitale <michaeldba@sqlexec.com&= gt;
Cc: pgsql-admin@lists.postgresql.org
Sub= ject: [EXT] Re: Improve "select count(*)" query - takes more than = 30 mins for some large tables

What's wrong with parallelism? That's why it wa= s invented. If you really need an accurate count at moment's notice, create= a trigger to maintain it.

Regards
On Tue, Jul 12, 2022, 10:31 AM MichaelDBA Vita= le <michaeldba@sqlexec.com= > wrote:
Perhaps do an analyze on the table and then = select reltuples from pg_class for that table. Might be faster than t= he select count(*).

Regards,

Michael Vitale
On 07/12/2022 8:51 AM Mladen Gogala <= gogala.mladen@gmail.com> wrote:

On 7/11/22 03:23, Florents Tselai wrote:
psql =E2=80=9Cselect id from my_table" | sort -u | wc -l =
;  
That will be a lot slower than just "select count(*) from my_tab= le". You are delivering data to the user program (psql) and then ship= ping them to pipe and then processing the output with "wc". Depending on th= e version, PostgreSQL has very reliable parallelism and can do counting rat= her quickly. The speed of "select count(*) from my_table" depends on the sp= eed of I/O. Since the table is big, it cannot be cached in the file system = cache, so all that you have at your disposal is the raw disk speed. For the= smaller machines, NVME is the king. For larger rigs, you should consider s= omething like Pure, XTremIO or NetApp SolidFire. People frequently expect d= atabase to do miracles with under par hardware.
-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com