| From: | Scott Ribe <scott_ribe(at)elevated-dev(dot)com> |
|---|---|
| To: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com> |
| Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "Pierson Patricia L (Contractor)" <Patricia(dot)L(dot)Pierson(at)irs(dot)gov>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: [EXT] Improve "select count(*)" query - takes more than 30 mins for some large tables |
| Date: | 2022-07-13 02:28:42 |
| Message-ID: | 647F11EE-BBDA-46C1-8591-96B2C1DCE66C@elevated-dev.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
> On Jul 12, 2022, at 8:12 PM, MichaelDBA <MichaelDBA(at)sqlexec(dot)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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mladen Gogala | 2022-07-13 02:54:50 | Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables |
| Previous Message | MichaelDBA | 2022-07-13 02:12:56 | Re: [EXT] Re: Improve "select count(*)" query - takes more than 30 mins for some large tables |