From: Tom Lane <tgl@sss.pgh.pa.us>
To: Fd Habash <fmhabash@gmail.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
In-reply-to: <5b9a9f81.1c69fb81.43388.0e8b@mx.google.com>
References: <5b9a9f81.1c69fb81.43388.0e8b@mx.google.com>
Comments: In-reply-to Fd Habash <fmhabash@gmail.com>
	message dated "Thu, 13 Sep 2018 13:33:54 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <13500.1536862322.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 13 Sep 2018 14:12:02 -0400
Message-ID: <13501.1536862322@sss.pgh.pa.us>
Precedence: bulk

Fd Habash <fmhabash@gmail.com> writes:
> Based on my research in the forums and Google , it is described in multi=
ple places that =E2=80=98select count(*)=E2=80=99 is expected to be slow i=
n Postgres because of the MVCC controls imposed upon the query leading a t=
able 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 be expe=
cted. =


> 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; =

>                                                     QUERY PLAN          =
                                          =

> ------------------------------------------------------------------------=
------------------------------------------
>  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