From: "Schneider, Jeremy" <schnjere@amazon.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
CC: Fred Habash <fmhabash@gmail.com>,
        "andres@anarazel.de"
	<andres@anarazel.de>,
        "tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>,
        "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Thread-Topic: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Thread-Index: AQHUT7ew3W/Pfb+sIEq/I+LoNlFjVw==
Date: Wed, 19 Sep 2018 01:25:46 +0000
Message-ID: <8E4AC460-ED4A-4AB4-B19E-F8CB5E94B04F@amazon.com>
References: <5b9a9f81.1c69fb81.43388.0e8b@mx.google.com>
 <13501.1536862322@sss.pgh.pa.us>
 <20180913194347.sjnui3hajk7amcx6@alap3.anarazel.de>
 <CADpeV5zJNzy10g_-NZ6Y_ppJy_CoJ0o2X=mapDjrpUmJojd0hg@mail.gmail.com>
 <53497a4c61a606e0b144b9f9c1426536e4e1f358.camel@cybertec.at>
In-Reply-To: <53497a4c61a606e0b144b9f9c1426536e4e1f358.camel@cybertec.at>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-ID: <FA4DC7BECB2A1D429C8D29FCB63B9C41@amazon.com>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: Bulk
Precedence: bulk

Aside from I/O going to a different kind of storage, I don't think anything=
 Aurora-specific should be at play here.

Would the 118 million buffer accesses (hits+reads) only include the index s=
can, or would that number also reflect buffers accessed for the 500 million=
 heap fetches?

While Aurora doesn't have a filesystem cache (since it's a different kind o=
f storage), it does default the buffer_cache to 75% to offset this. It appe=
ars that as Laurenz has pointed out, this is simply a lot of I/O requests i=
n a serial process.=20

BTW that's 900GB of data that was read (118 million buffers of 8k each) - o=
n a box with only 61GB of memory available for caching.

-Jeremy

Sent from my TI-83

> On Sep 17, 2018, at 12:04 PM, Laurenz Albe <laurenz.albe@cybertec.at> wro=
te:
>=20
> Fred Habash wrote:
>> If I'm reading this correctly, it took 57M ms out of an elapsed time of =
61M ms to read 45M pages from the filesystem?
>> If the average service time per sarr is < 5 ms, Is this a case of bloate=
d index where re-indexing is warranted?=20
>>=20
>> explain (analyze,buffers,timing,verbose,costs)
>> select count(*) from jim.pitations ;
>>                                                                         =
        QUERY PLAN                                                         =
                       =20
>> ------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------
>> Aggregate  (cost=3D72893810.73..72893810.74 rows=3D1 width=3D8) (actual =
time=3D61141110.437..61141110.437 rows=3D1 loops=3D1)
>>   Output: count(*)
>>   Buffers: shared hit=3D72620045 read=3D45297330
>>   I/O Timings: read=3D57489958.088
>>   ->  Index Only Scan using pit_indx_fk03 on jim.pitations  (cost=3D0.58=
..67227187.37 rows=3D2266649344 width=3D0) (actual time=3D42.327..60950272.=
189 rows=3D2269623575 loops=3D1)
>>         Output: vsr_number
>>         Heap Fetches: 499950392
>>         Buffers: shared hit=3D72620045 read=3D45297330
>>         I/O Timings: read=3D57489958.088
>> Planning time: 14.014 ms
>> Execution time: 61,141,110.516 ms
>> (11 rows)
>=20
> 2269623575 / (45297330 + 72620045) ~ 20, so you have an average 20
> items per block.  That is few, and the index seems indeed bloated.
>=20
> Looking at the read times, you average out at about 1 ms per block
> read from I/O, but with that many blocks that's of course still a long ti=
me.
>=20
> Yours,
> Laurenz Albe
> --=20
> Cybertec | https://www.cybertec-postgresql.com
>=20
>=20