Message-ID: <3dc59390-b2ab-4ccb-3e3f-cc7a1c095829@gmail.com>
Date: Wed, 13 Jul 2022 08:16:27 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: [EXT] Improve "select count(*)" query - takes more than 30 mins
 for some large tables
Content-Language: en-US
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>
 <1037158693.49441.1657650345834@email.ionos.com>
 <05fa08bf8b4e1023eb879bcab0f83d428e4f7a43.camel@cybertec.at>
 <b76730ff-dd92-d4d6-60cf-64d00dc4db43@sqlexec.com>
 <647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/3dc59390-b2ab-4ccb-3e3f-cc7a1c095829%40gmail.com>
Precedence: bulk

On 7/12/22 21:28, Scott Ribe wrote:
>> On Jul 12, 2022, at 8:12 PM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>>
>> 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,

How recently is "recently"?  And does VACUUM clean it up?

-- 
Angular momentum makes the world go 'round.