Re: I/O on select count(*)

From: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>
To: "Greg Smith" <gsmith(at)gregsmith(dot)com>
Cc: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: I/O on select count(*)
Date: 2008-05-15 02:40:58
Message-ID: 2e78013d0805141940x50c3f51u439019e8e592959@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, May 15, 2008 at 7:51 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
>
>
> So is vacuum helpful here because it will force all that to happen in one
> batch? To put that another way: if I've run a manual vacuum, is it true
> that it will have updated all the hint bits to XMIN_COMMITTED for all the
> tuples that were all done when the vacuum started?
>

Yes. For that matter, even a plain SELECT or count(*) on the entire
table is good enough. That will check every tuple for visibility and
set it's hint bits.

Another point to note is that the hint bits are checked and set on a
per tuple basis. So especially during index scan, the same heap page
may get rewritten many times. I had suggested in the past that
whenever we set hint bits for a tuple, we should check all other
tuples in the page and set their hint bits too to avoid multiple
writes of the same page. I guess the idea got rejected because of lack
of benchmarks to prove the benefit.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Luke Lonergan 2008-05-15 02:52:01 Re: I/O on select count(*)
Previous Message Jan de Visser 2008-05-15 02:38:08 Re: I/O on select count(*)