Обсуждение: why select count(*) consumes wal logs

PG 10.5

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?  
I suspect vaccum is getting triggered, but this is a brand new table with no updates. So it should not.

Is there a SQL to peek into what PG is doing to write so much to WAL logs ?

Must be something to do with Vaccum as the second time I ran the SQL, it did not consume WAL logs.

On Tue, Nov 6, 2018 at 11:08 AM Ravi Krishna <srkrishna1@aol.com> wrote:

PG 10.5

I loaded 133 million rows to a wide table (more than 100 cols) via COPY.

It's always a good idea after doing a large scale data load to do a vacuum analyze on the table (or the entire database.)

Ravi Krishna <srkrishna1@aol.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

>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.

yes now I recollect reading this in a blog.  Thanks Tom.

Michael Nolan <htfoot@gmail.com> writes:
> On Tue, Nov 6, 2018 at 11:40 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> Tom, does that include ANALYZE?

Yes, but remember that ANALYZE doesn't scan the whole table; it'll only
set the bit on rows it visits.

(I forget at the moment if it's guaranteed to set the bit on all rows
in each page it examines, or only on the rows it selects to sample.
But in any case it will not examine every page in the table.)

            regards, tom lane

> As long as you don’t have page checksums turned on,
> you can prevent this by turning off wal_log_hints.

  


I did not run initdb. How to find out which parameter were used with initdb. For page checksums
to be on, it must have been run with -k option.

Our wal_log_hints is left at default which means off.

thanks

> select data_page_checksum_version from pg_control_init()

returned 1.  So we have page_checksum turned on, and wal_log_hints off.

Ravi Krishna schrieb am 07.11.2018 um 15:10:
> 
>> select data_page_checksum_version from pg_control_init()
> 
> returned 1.  So we have page_checksum turned on, and wal_log_hints off.

If page_checksum is enabled, then wal_log_hints is ignored (or actually always assumed "on")