On Tue, 7 Apr 2020 at 02:40, Peter Geoghegan <p...@bowt.ie> wrote: > > On Mon, Apr 6, 2020 at 2:21 AM Amit Kapila <amit.kapil...@gmail.com> wrote: > > AFAIU, it uses heapam_index_build_range_scan but for writing to index, > > it doesn't use buffer manager. > > Right. It doesn't need to use the buffer manager to write to the > index, unlike (say) GIN's CREATE INDEX.
Hmm, after more thoughts and testing, it seems to me that parallel btree index creation uses buffer manager while scanning the table in parallel, i.e in heapam_index_build_range_scan, which affects shared_blks_xxx in pg_stat_statements. I've some parallel create index tests with the current HEAD and with the attached patch. The table has 44248 blocks. HEAD, no workers: -[ RECORD 1 ]-------+---------- total_plan_time | 0 total_plan_time | 0 shared_blks_hit | 148 shared_blks_read | 44281 total_read_blks | 44429 shared_blks_dirtied | 44261 shared_blks_written | 24644 wal_records | 71693 wal_num_fpw | 71682 wal_bytes | 566815038 HEAD, 4 workers: -[ RECORD 1 ]-------+---------- total_plan_time | 0 total_plan_time | 0 shared_blks_hit | 160 shared_blks_read | 8892 total_read_blks | 9052 shared_blks_dirtied | 8871 shared_blks_written | 5342 wal_records | 71693 wal_num_fpw | 71682 wal_bytes | 566815038 The WAL usage statistics are good but the buffer usage statistics seem not correct. Patched, no workers: -[ RECORD 1 ]-------+---------- total_plan_time | 0 total_plan_time | 0 shared_blks_hit | 148 shared_blks_read | 44281 total_read_blks | 44429 shared_blks_dirtied | 44261 shared_blks_written | 24843 wal_records | 71693 wal_num_fpw | 71682 wal_bytes | 566815038 Patched, 4 workers: -[ RECORD 1 ]-------+---------- total_plan_time | 0 total_plan_time | 0 shared_blks_hit | 172 shared_blks_read | 44282 total_read_blks | 44454 shared_blks_dirtied | 44261 shared_blks_written | 26968 wal_records | 71693 wal_num_fpw | 71682 wal_bytes | 566815038 Buffer usage statistics seem correct. The small differences would be catalog lookups Peter mentioned. Regards, -- Masahiko Sawada http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
bufferusage_create_index_v4.patch
Description: Binary data
