Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0XOl-000892-IM for pgsql-performance@arkaria.postgresql.org; Thu, 13 Sep 2018 19:35:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g0XOj-000078-Gh for pgsql-performance@arkaria.postgresql.org; Thu, 13 Sep 2018 19:35:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0XOj-00006v-7N for pgsql-performance@lists.postgresql.org; Thu, 13 Sep 2018 19:35:29 +0000 Received: from mail-io1-xd35.google.com ([2607:f8b0:4864:20::d35]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g0XOf-0001vd-CN for pgsql-performance@lists.postgresql.org; Thu, 13 Sep 2018 19:35:28 +0000 Received: by mail-io1-xd35.google.com with SMTP id n18-v6so4114272ioa.9 for ; Thu, 13 Sep 2018 12:35:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:cc:from:subject:date:importance :in-reply-to:references; bh=fygZ3ELY0E+9ttHhgKIUUVj4/k29PQxSWmL2yLYTou0=; b=nIVo9DtHUkQ/ya8oTNebGHrCyPtxKJfBJSQP9lVR8dfkmt794qB9iUzgp3iahnHTAG iy2WNC69IVQo7mnfCdyeDR1NMCoQw/9+Eh7YU27WlTVVtunhhatZt1BjOLDlFFeHWcL5 CAyWAi2U/VoJVAmQhi4fWepoXRp47W0d8GNYxR4jvUiOownT2eXyBHKotamlb7Nfv5Cl NyelIquu1TglPH/RaFRKdKYLdI5Cw6yjISgcfT4FUyx9anvOKIVDi+CEY0UOLHIVFO7S Vm8kC4tAFWvIQXIcNpFJ2h6hOmR7436DYVMO68tdaA1Xmqng7dgNuF9x6szlhJDvm+uk /+Og== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:cc:from:subject:date :importance:in-reply-to:references; bh=fygZ3ELY0E+9ttHhgKIUUVj4/k29PQxSWmL2yLYTou0=; b=SBfCMOe3BYVsCtRmZtlOBjGlA0iB4aQMZ5aSmUyzGjhbr8EQnyqX0l+/SDY//9iPg7 0jNFccCF27yRuAUAhtozWGIXFHqxW3e0NWTtjIcfQhsXMIxXRB4/L5y0Q/PP0c9uflMD C4x5czVZO4J0Z8GVlThSj2nx5jz0A9vNHl4427Vsn+5T0GRqjUzh6IcP74nIypJDgpcR 5GVUEe9DzaICGRfuw4Ghfr036OUOw4wrTgVa0NMkefpXi2hhhBS62k8Tc8PqtkG0H2iG jBri9n7QZyzkYb8/59AUe4WfIzLUw17xvE2eY8GUNsCf4PH4xGSGFDPUdPSPC1EDuAVl XxIg== X-Gm-Message-State: APzg51BdwMEWVMKFHlh7l+s27uZZBkK8Trjxkh+AmTrl1DvQ1u0+qzlI 4IDuXLB8rfby8eDIxFjHPAV78ipLKLo= X-Google-Smtp-Source: ANB0Vdakk4X4fWxgg1KrxC22/JE3SnN/iuK/61I3qspFslXw4BUinF/+CIc7tgwliCN4kJpvzmCdPw== X-Received: by 2002:a6b:7117:: with SMTP id q23-v6mr1744928iog.37.1536867323056; Thu, 13 Sep 2018 12:35:23 -0700 (PDT) Received: from ?IPv6:::ffff:172.25.137.157? ([165.215.94.3]) by smtp.gmail.com with ESMTPSA id r126-v6sm2123450ita.26.2018.09.13.12.35.22 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 13 Sep 2018 12:35:22 -0700 (PDT) Message-ID: <5b9abbfa.1c69fb81.ecad0.d1fd@mx.google.com> MIME-Version: 1.0 To: Tom Lane Cc: "pgsql-performance@lists.postgresql.org" From: Fd Habash Subject: RE: Select count(*) on a 2B Rows Tables Takes ~20 Hours Date: Thu, 13 Sep 2018 15:35:23 -0400 Importance: normal X-Priority: 3 In-Reply-To: <13501.1536862322@sss.pgh.pa.us> References: <5b9a9f81.1c69fb81.43388.0e8b@mx.google.com> <13501.1536862322@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="_3E32F612-B7F3-4024-B17E-844F4ADA647F_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_3E32F612-B7F3-4024-B17E-844F4ADA647F_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Just checked metrics while the count was running =E2=80=A6 Read latency < 3.5 ms Write latency < 4 ms Read throughput ~ 40 MB/sec with sporadic peaks at 100 Read IOPS ~ 5000 QDepth < 3 ---------------- Thank you From: Tom Lane Sent: Thursday, September 13, 2018 2:12 PM To: Fd Habash Cc: pgsql-performance@lists.postgresql.org Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours Fd Habash writes: > Based on my research in the forums and Google , it is described in multip= le places that =E2=80=98select count(*)=E2=80=99 is expected to be slow in = Postgres because of the MVCC controls imposed upon the query leading a tabl= e scan. Also, the elapsed time increase linearly with table size.=20 > However, I do not know if elapsed time I=E2=80=99m getting is to be expec= ted.=20 > Table reltuples in pg_class =3D 2,266,649,344 (pretty close) > Query =3D select count(*) from jim.sttyations ; > Elapsed time (ET) =3D 18.5 hrs That's pretty awful. My recollection is that in recent PG releases, SELECT COUNT(*) runs at something on the order of 100ns/row given an all-in-memory table. Evidently you're rather badly I/O bound. > This is an Aurora cluster running on r4.2xlarge (8 vCPU, 61g). Don't know much about Aurora, but I wonder whether you paid for guaranteed (provisioned) IOPS, and if so what service level. > refpep-> select count(*) from jim.sttyations;=20 > QUERY PLAN = =20 > -------------------------------------------------------------------------= ----------------------------------------- > Aggregate (cost=3D73451291.77..73451291.78 rows=3D1 width=3D8) > Output: count(*) > -> Index Only Scan using stty_indx_fk03 on jim.sttyations (cost=3D0.= 58..67784668.41 rows=3D2266649344 width=3D0) > Output: vsr_number > (4 rows) Oh, hmm ... the 100ns figure I mentioned was for a seqscan. IOS could be a lot worse for a number of reasons, foremost being that if the table isn't mostly all-visible then it'd involve a lot of random heap access. It might be interesting to try forcing a seqscan plan (see enable_indexscan). regards, tom lane --_3E32F612-B7F3-4024-B17E-844F4ADA647F_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Just checked metrics while the count= was running =E2=80=A6

 

Read latency < 3.5 ms

Write latenc= y < 4 ms

Read throughput ~ 40 MB/sec with sporad= ic peaks at 100

Read IOPS ~ 5000

QDepth < 3

 

 

----------------
= Thank you

 

= From: Tom Lane
Sent: Thursday, September 13, 2018 2:12 PM
To: Fd Habash
Cc: pgsql-performance@lists.postgresql.org
= Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours

 

Fd Habash= <fmhabash@gmail.com> writes:

> Based on m= y research in the forums and Google , it is described in multiple places th= at =E2=80=98select count(*)=E2=80=99 is expected to be slow in Postgres bec= ause of the MVCC controls imposed upon the query leading a table scan. Also= , the elapsed time increase linearly with table size.

> However, I do not know if elapsed time I=E2=80=99m getting is to b= e expected.

 

> Table reltuples in pg_class =3D 2,266,649,344 (pretty close)

> Query =3D select count(*) from jim.sttyations ;

=

> Elapsed time (ET) =3D 18.5 hrs

 

That's pretty awful. =C2=A0= My recollection is that in recent PG releases,

SELE= CT COUNT(*) runs at something on the order of 100ns/row given an

all-in-memory table.=C2=A0 Evidently you're rather badly I/O = bound.

 

&g= t; This is an Aurora cluster running on r4.2xlarge (8 vCPU, 61g).

 

Don't know much ab= out Aurora, but I wonder whether you paid for

guara= nteed (provisioned) IOPS, and if so what service level.

 

> refpep-> select coun= t(*) from jim.sttyations;

>=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=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=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 Aggregate=C2=A0 (cost=3D734512= 91.77..73451291.78 rows=3D1 width=3D8)

>=C2=A0= =C2=A0=C2=A0 Output: count(*)

>=C2=A0=C2=A0=C2= =A0 ->=C2=A0 Index Only Scan using stty_indx_fk03 on jim.sttyations=C2= =A0 (cost=3D0.58..67784668.41 rows=3D2266649344 width=3D0)

>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Output: = vsr_number

> (4 rows)

 

Oh, hmm ... the 100ns figure I ment= ioned was for a seqscan.=C2=A0 IOS

could be a lot w= orse for a number of reasons, foremost being that

i= f the table isn't mostly all-visible then it'd involve a lot of

random heap access.=C2=A0 It might be interesting to try forci= ng a

seqscan plan (see enable_indexscan).

 

=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 regards, tom lane

 

= --_3E32F612-B7F3-4024-B17E-844F4ADA647F_--