Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.100.31\))
Subject: Re: [EXT] Improve "select count(*)" query - takes more than 30 mins
 for some large tables
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <b76730ff-dd92-d4d6-60cf-64d00dc4db43@sqlexec.com>
Date: Tue, 12 Jul 2022 20:28:42 -0600
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 "Pierson Patricia L (Contractor)" <Patricia.L.Pierson@irs.gov>,
 "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com>
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>
 <1037158693.49441.1657650345834@email.ionos.com>
 <05fa08bf8b4e1023eb879bcab0f83d428e4f7a43.camel@cybertec.at>
 <b76730ff-dd92-d4d6-60cf-64d00dc4db43@sqlexec.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Archived-At: 
 <https://www.postgresql.org/message-id/647F11EE-BBDA-46C1-8591-96B2C1DCE66C%40elevated-dev.com>
Precedence: bulk

> On Jul 12, 2022, at 8:12 PM, MichaelDBA <MichaelDBA@sqlexec.com> =
wrote:
>=20
> Oh really? Even if it did an INDEX ONLY SCAN, it would still be slower =
because it still has to access the heap in a random I/O kinda way  (like =
I said-->"The heap always gets accessed for select counts") because =
visibility info is only found in the HEAP not the index.

This changed in 9.something. There is now a visibility map, which can, =
for data that hasn't changed recently, greatly reduce the amount of =
access required to the heap to determine visibility.=