pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Scott Ribe <scott_ribe@elevated-dev.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pierson Patricia L (Contractor) <Patricia.L.Pierson@irs.gov>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables
Date: Tue, 12 Jul 2022 20:28:42 -0600
Message-ID: <647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com> (raw)
In-Reply-To: <b76730ff-dd92-d4d6-60cf-64d00dc4db43@sqlexec.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>

> 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, greatly reduce the amount of access required to the heap to determine visibility.




view thread (15+ messages)  latest in thread

Message-ID: <647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com>
Permalink:  ../647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com/
Also on:    postgresql.org/message-id/647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: scott_ribe@elevated-dev.com, MichaelDBA@sqlexec.com, laurenz.albe@cybertec.at, Patricia.L.Pierson@irs.gov, pgsql-admin@lists.postgresql.org
  Subject: Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables
  In-Reply-To: <647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox