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 <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1oBGv8-0006im-H1
	for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jul 2022 14:31:26 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1oBGv7-0006lr-EA
	for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jul 2022 14:31:25 +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 <michaeldba@sqlexec.com>)
	id 1oBGv7-0006lc-3w
	for pgsql-admin@lists.postgresql.org; Tue, 12 Jul 2022 14:31:25 +0000
Received: from mout.perfora.net ([74.208.4.196])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <michaeldba@sqlexec.com>)
	id 1oBGv3-0002Nd-BW
	for pgsql-admin@lists.postgresql.org; Tue, 12 Jul 2022 14:31:24 +0000
Received: from oxuslxaltgw14.schlund.de ([10.72.76.70]) by mrelay.perfora.net
 (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Lg28r-1nnGtJ1kfT-00pb33;
 Tue, 12 Jul 2022 16:31:18 +0200
Date: Tue, 12 Jul 2022 10:31:17 -0400 (EDT)
From: MichaelDBA Vitale <michaeldba@sqlexec.com>
To: Mladen Gogala <gogala.mladen@gmail.com>,
	pgsql-admin@lists.postgresql.org
Message-ID: <1221536526.20277.1657636277921@email.ionos.com>
In-Reply-To: <1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com>
References: 
 <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
 <1B1927BA-F7DE-40E8-8F67-BCC001CD4C6B@gmail.com>
 <1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com>
Subject: 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:rMOQXpdU6I8nbmwMYcluyFG6UtAHnRFLBh2zjaKXQB2aoBtxefq
 31c4zfz5HQBjqLH7EdcrJ3IqDwxg5Ip/onyKJSJWzSch0t5CsdVTG9qnfAKD1Xz37V0IGrm
 ezNd3YOEAmGsOYCGM36Lf3cK+baShCA1l6FXkFXhMhqwPKsRuUoG/dpDYK7r4NhSEF8qpoW
 n45bwu1/hQsn4ID6+PEww==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:+ESdjz/YDlY=:j7511bgxfarj7bygqyBiI9
 BEVClHFx40WYWuzBnFNcmBgupp0m5DORUplfZ5NA5L2aujKCyU4krFGgQnJJKOe08Y9XQtz/u
 HlzyKisea4FLUYcWniuUPhThjWbXdifjkH8PdCJbHPSUY2dvpT9ueLCIJVHoyW38Cda/xhhdU
 piOsS6ArzvtTUg8HN24INmyKot7grPgigcCPUiL2JmeSgW8lZRb2LyggiWxvzv0YFElw7YJ+7
 9y7m7snHeqBW6Akg/DvtHzyGUjjsBQgComct/mcv/cIRmXx7JURLL4u9s5GgTMvJkSEwpXKD/
 xwkoLG+XhDjqYhPHWUWyGowuFBzQWN3AUXE33Nk4tfVVMq9nsXVqsM+HZ64VGlkpy0CMK431A
 iGe7ToDkSIU4xy5OYrxOGqMddX7rq+YkdzkhZQE/tUzKC+tFI9xwBZlU9WGap8XsQQbosZrUj
 9nW0E1PFHiedgWosTDF8Fi0cEhCf15QAwZPfl7JFRnP+BmKjxqVGPQcek1VpZTAtq4KG7jkLG
 NmKzEqPJIdEYQvl4KXfjX364dRewpKTA7cQ+KnsMmJbGEcmTEFuCnXhWGrQofTgm+GavU2OOR
 CmtUIelxX9GgoADPFC5rDiuzVXWAf4kEp+81GIYxkd//PR5fWYuV0PhvNaeiJc+5dGSmZlypy
 SEVkVELct5olsoQyd8U2+RVvIfcYbmBHvQPxxJMjrZT2enQxrNc5YdGlEyGtw5bfEbSH/aNKm
 Ag5Qq2PxgJ+QTHHG
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/1221536526.20277.1657636277921%40email.ionos.com>
Precedence: bulk

<!doctype html>
<html>
 <head>=20
  <meta charset=3D"UTF-8">=20
 </head>
 <body>
  <div>
   Perhaps do an analyze on the table and then select reltuples from pg_cla=
ss for that table.&nbsp; Might be faster than the select count(*).
  </div>
  <div class=3D"default-style">
   <br>
  </div>
  <div class=3D"default-style">
   Regards,
  </div>
  <div class=3D"default-style">
   Michael Vitale
  </div>
  <div class=3D"default-style">
   <br>
  </div>
  <blockquote type=3D"cite">
   <div>
    On 07/12/2022 8:51 AM Mladen Gogala &lt;gogala.mladen@gmail.com&gt; wro=
te:
   </div>
   <div>
    <br>
   </div>
   <div>
    <br>
   </div>
   <div class=3D"moz-cite-prefix">
    On 7/11/22 03:23, Florents Tselai wrote:
    <br>
   </div>
   <blockquote type=3D"cite">
    <pre class=3D"moz-quote-pre">psql =E2=80=9Cselect id from my_table" | s=
ort -u | wc -l  =20
</pre>
   </blockquote>
   <p>That will be a lot slower than just "select count(*) from my_table".&=
nbsp; You are delivering data to the user program (psql) and then shipping =
them to pipe and then processing the output with "wc". Depending on the ver=
sion, PostgreSQL has very reliable parallelism and can do counting rather q=
uickly. The speed of "select count(*) from my_table" depends on the speed o=
f 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 smal=
ler machines, NVME is the king. For larger rigs, you should consider someth=
ing like Pure, XTremIO or NetApp SolidFire. People frequently expect databa=
se to do miracles with under par hardware.<br></p>
   <pre class=3D"moz-signature">--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a href=3D"https://dbwhisperer.wordpress.com" class=3D"moz-txt-link-freetex=
t">https://dbwhisperer.wordpress.com</a>
</pre>
  </blockquote>
 </body>
</html>