Re: why select count(*) consumes wal logs

Le mar. 6 nov. 2018 12:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> Ravi Krishna <srkrishna1(at)aol(dot)com> writes:
> > I loaded 133 million rows to a wide table (more than 100 cols) via COPY.
> The table has
> > no index at this time. Since I am the only user I don't see any other
> activity.
> > Now when I run select count(*) on the table where I just loaded data, it
> runs for ever,
> > more than 10min and still running. Intrigued, I checked locks and saw
> nothing. Then I noticed something
> > strange. When select count(*) runs, PG is writing to wal_logs, and that
> too a large amount. Why?
>
> That represents setting the yes-this-row-is-committed hint bits on the
> newly loaded rows. The first access to any such row will set that bit,
> whether it's a select or a VACUUM or whatever.
>
> regards, tom lane
>

And IIRC, it can generate a high WAL traffic since the first page change
after a checkpoint is done with full page write. And you said that it's
happening on a big table with wide rows....

>