Message-ID: <6e9caa48-5e29-a493-d54d-3a97f3893029@jakobs.com>
Date: Tue, 12 Jul 2022 20:25:32 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.10.0
Content-Language: de-DE
To: pgsql-admin@lists.postgresql.org
References: 
 <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
 <1B1927BA-F7DE-40E8-8F67-BCC001CD4C6B@gmail.com>
 <1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com>
 <1221536526.20277.1657636277921@email.ionos.com>
 <CALcG2D+JJMK13U3qV_pn8np+m2urGJ7iyj_hB0Mt9XfM8GU-vw@mail.gmail.com>
 <SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.com>
From: Holger Jakobs <holger@jakobs.com>
Subject: Re: [EXT] Re: Improve "select count(*)" query - takes more than 30
 mins for some large tables
In-Reply-To: 
 <SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------oIK0ZBIBH2bwRM0wO4y8vXCT"
Archived-At: 
 <https://www.postgresql.org/message-id/6e9caa48-5e29-a493-d54d-3a97f3893029%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------oIK0ZBIBH2bwRM0wO4y8vXCT
Content-Type: multipart/mixed; boundary="------------ImPBuIF0KUGBpeDrMdPKqUcR";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <6e9caa48-5e29-a493-d54d-3a97f3893029@jakobs.com>
Subject: Re: [EXT] Re: Improve "select count(*)" query - takes more than 30
 mins for some large tables
References: <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
 <1B1927BA-F7DE-40E8-8F67-BCC001CD4C6B@gmail.com>
 <1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com>
 <1221536526.20277.1657636277921@email.ionos.com>
 <CALcG2D+JJMK13U3qV_pn8np+m2urGJ7iyj_hB0Mt9XfM8GU-vw@mail.gmail.com>
 <SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.com>
In-Reply-To: <SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.com>

--------------ImPBuIF0KUGBpeDrMdPKqUcR
Content-Type: multipart/alternative;
 boundary="------------2YJn32lozo5Ky7DA7ZYXCQsJ"

--------------2YJn32lozo5Ky7DA7ZYXCQsJ
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64

QW0gMTIuMDcuMjIgdW0gMjA6MTMgc2NocmllYiBQaWVyc29uIFBhdHJpY2lhIEwgKENvbnRy
YWN0b3IpOg0KPg0KPiBIZWxsbywNCj4NCj4gRG8gYSBjb3VudCBvbiB0aGUgcHJpbWFyeSBr
ZXkuwqAgV2lsbCBmb3JjZSBpbmRleCBhY2Nlc3MgYW5kIHlvdSBkb27igJl0IA0KPiBhY2Nl
c3MgdGhlIGVudGlyZSByb3cgd2hpY2ggbWF5IGJlIHZlcnkgbG9uZy4NCj4NCj4gTElLRSA6
IHNlbGVjdCBjb3VudChJRCkgZnJvbSBteV90YWJsZTsNCj4NCklmIHlvdSBkbyBhIENPVU5U
KCopIHRoZSBkYXRhYmFzZSB3b24ndCBkbyBhbnl0aGluZyBkaWZmZXJlbnQuIFRoYXQncyBh
IA0KbXl0aC4NCg0KSnVzdCBjb21wYXJlIHRoZSBleGVjdXRpb24gcGxhbnMuDQoNCg0KZGI9
IyBleHBsYWluIHNlbGVjdCBjb3VudChhbnIpIGZyb20gYXVmdHJhZzsNCiDCoMKgwqDCoMKg
wqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKg
wqDCoMKgwqDCoMKgwqAgUVVFUlkgUExBTg0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tDQogwqBGaW5hbGl6ZSBBZ2dyZWdhdGXCoCAoY29zdD0xMTYxNC41NS4uMTE2
MTQuNTYgcm93cz0xIHdpZHRoPTgpDQogwqDCoCAtPsKgIEdhdGhlcsKgIChjb3N0PTExNjE0
LjMzLi4xMTYxNC41NCByb3dzPTIgd2lkdGg9OCkNCiDCoMKgwqDCoMKgwqDCoMKgIFdvcmtl
cnMgUGxhbm5lZDogMg0KIMKgwqDCoMKgwqDCoMKgwqAgLT7CoCBQYXJ0aWFsIEFnZ3JlZ2F0
ZcKgIChjb3N0PTEwNjE0LjMzLi4xMDYxNC4zNCByb3dzPTEgd2lkdGg9OCkNCiDCoMKgwqDC
oMKgwqDCoMKgwqDCoMKgwqDCoMKgIC0+wqAgUGFyYWxsZWwgU2VxIFNjYW4gb24gYXVmdHJh
ZyAoY29zdD0wLjAwLi45NTcyLjY3IA0Kcm93cz00MTY2Njcgd2lkdGg9OCkNCig1IFplaWxl
bikNCg0KZGI9IyBleHBsYWluIHNlbGVjdCBjb3VudCgqKSBmcm9tIGF1ZnRyYWc7DQogwqDC
oMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDC
oMKgwqDCoMKgwqDCoMKgwqDCoMKgIFFVRVJZIFBMQU4NCi0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLQ0KIMKgRmluYWxpemUgQWdncmVnYXRlwqAgKGNvc3Q9MTE2MTQu
NTUuLjExNjE0LjU2IHJvd3M9MSB3aWR0aD04KQ0KIMKgwqAgLT7CoCBHYXRoZXLCoCAoY29z
dD0xMTYxNC4zMy4uMTE2MTQuNTQgcm93cz0yIHdpZHRoPTgpDQogwqDCoMKgwqDCoMKgwqDC
oCBXb3JrZXJzIFBsYW5uZWQ6IDINCiDCoMKgwqDCoMKgwqDCoMKgIC0+wqAgUGFydGlhbCBB
Z2dyZWdhdGXCoCAoY29zdD0xMDYxNC4zMy4uMTA2MTQuMzQgcm93cz0xIHdpZHRoPTgpDQog
wqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoCAtPsKgIFBhcmFsbGVsIFNlcSBTY2FuIG9u
IGF1ZnRyYWcgKGNvc3Q9MC4wMC4uOTU3Mi42NyANCnJvd3M9NDE2NjY3IHdpZHRoPTApDQoo
NSBaZWlsZW4pDQoNCi0tIA0KSG9sZ2VyIEpha29icywgQmVyZ2lzY2ggR2xhZGJhY2gsIFRl
bC4gKzQ5LTE3OC05NzU5MDEyDQoNCg==
--------------2YJn32lozo5Ky7DA7ZYXCQsJ
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 12.07.22 um 20:13 schrieb Pierson
      Patricia L (Contractor):<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09=
=2Eprod.outlook.com">
      <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DU=
TF-8">
      <meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered
        medium)">
      <style>@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}@font-face
	{font-family:Consolas;
	panose-1:2 11 6 9 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}pre
	{mso-style-priority:99;
	mso-style-link:"HTML Preformatted Char";
	margin:0in;
	font-size:10.0pt;
	font-family:"Courier New";}span.HTMLPreformattedChar
	{mso-style-name:"HTML Preformatted Char";
	mso-style-priority:99;
	mso-style-link:"HTML Preformatted";
	font-family:Consolas;}span.EmailStyle21
	{mso-style-type:personal-reply;
	font-family:"Verdana",sans-serif;
	color:windowtext;}.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}div.WordSection1
	{page:WordSection1;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
      <div class=3D"WordSection1">
        <p class=3D"MsoNormal"><span
            style=3D"font-size:12.0pt;font-family:&quot;Verdana&quot;,san=
s-serif">Hello,<o:p></o:p></span></p>
        <p class=3D"MsoNormal"><span
            style=3D"font-size:12.0pt;font-family:&quot;Verdana&quot;,san=
s-serif"><o:p>=C2=A0</o:p></span></p>
        <p class=3D"MsoNormal"><span
            style=3D"font-size:12.0pt;font-family:&quot;Verdana&quot;,san=
s-serif">Do
            a count on the primary key.=C2=A0 Will force index access and=
 you
            don=E2=80=99t access the entire row which may be very long.<o=
:p></o:p></span></p>
        <p class=3D"MsoNormal"><span
            style=3D"font-size:12.0pt;font-family:&quot;Verdana&quot;,san=
s-serif">LIKE
            : select count(ID) from my_table;<o:p></o:p></span></p>
        <blockquote style=3D"border:none;border-left:solid #CCCCCC
          1.0pt;padding:0in 0in 0in
          6.0pt;margin-left:4.8pt;margin-right:0in">
          <div>
            <blockquote style=3D"margin-top:5.0pt;margin-bottom:5.0pt">
              <pre>
</pre>
            </blockquote>
          </div>
        </blockquote>
      </div>
    </blockquote>
    <p>If you do a COUNT(*) the database won't do anything different.
      That's a myth.</p>
    <p>Just compare the execution plans.</p>
    <p><br>
    </p>
    <p>db=3D# explain select count(anr) from auftrag;<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 QUERY
      PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 =C2=A0<br>
-------------------------------------------------------------------------=
-----------------<br>
      =C2=A0Finalize Aggregate=C2=A0 (cost=3D11614.55..11614.56 rows=3D1 =
width=3D8)<br>
      =C2=A0=C2=A0 -&gt;=C2=A0 Gather=C2=A0 (cost=3D11614.33..11614.54 ro=
ws=3D2 width=3D8)<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Workers Planned: 2=
<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -&gt;=C2=A0 Partia=
l Aggregate=C2=A0 (cost=3D10614.33..10614.34 rows=3D1
      width=3D8)<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 -&gt;=C2=A0 Parallel Seq Scan on auftrag=C2=A0
      (cost=3D0.00..9572.67 rows=3D416667 width=3D8)<br>
      (5 Zeilen)<br>
      <br>
      db=3D# explain select count(*) from auftrag;<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 QUERY
      PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 =C2=A0<br>
-------------------------------------------------------------------------=
-----------------<br>
      =C2=A0Finalize Aggregate=C2=A0 (cost=3D11614.55..11614.56 rows=3D1 =
width=3D8)<br>
      =C2=A0=C2=A0 -&gt;=C2=A0 Gather=C2=A0 (cost=3D11614.33..11614.54 ro=
ws=3D2 width=3D8)<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Workers Planned: 2=
<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -&gt;=C2=A0 Partia=
l Aggregate=C2=A0 (cost=3D10614.33..10614.34 rows=3D1
      width=3D8)<br>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 -&gt;=C2=A0 Parallel Seq Scan on auftrag=C2=A0
      (cost=3D0.00..9572.67 rows=3D416667 width=3D0)<br>
      (5 Zeilen)<br>
      <br>
    </p>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------2YJn32lozo5Ky7DA7ZYXCQsJ--

--------------ImPBuIF0KUGBpeDrMdPKqUcR--

--------------oIK0ZBIBH2bwRM0wO4y8vXCT
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYs28nAUDAAAAAAAKCRCKV5yTsxlhulzi
AJ40/wS2U9jHI0/OB0Td3d8JFJ9T7wCg2/PDUxDaOwxSsZZ/9Xt7aYdhGLY=
=vmag
-----END PGP SIGNATURE-----

--------------oIK0ZBIBH2bwRM0wO4y8vXCT--