From: | Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Peter Geoghegan <pg(at)bowt(dot)ie> |
Subject: | vacuum -vs reltuples on insert only index |
Date: | 2020-10-23 15:44:51 |
Message-ID: | 20201023174451.69e358f1@firost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello,
I've found a behavior change with pg_class.reltuples on btree index. With only
insert activity on a table, when an index is processed, its related reltuples
is set to 0. Here is a demo script:
-- force index cleanup
set vacuum_cleanup_index_scale_factor to 0;
drop table if exists t;
create table t as select i from generate_series(1, 100) i;
create index t_i on t(i);
-- after index creation its reltuples is correct
select reltuples from pg_class where relname = 't_i'
-- result: reltuples | 100
-- vacuum set index reltuples to 0
vacuum t;
select reltuples from pg_class where relname = 't_i'
-- result: reltuples | 0
-- analyze set it back to correct value
analyze t;
select reltuples from pg_class where relname = 't_i'
-- result: reltuples | 100
-- insert + vacuum reset it again to 0
insert into t values(101);
vacuum (verbose off, analyze on, index_cleanup on) t;
select reltuples from pg_class where relname = 't_i'
-- result: reltuples | 0
-- delete + vacuum set it back to correct value
delete from t where i=10;
vacuum (verbose off, analyze on, index_cleanup on) t;
select reltuples from pg_class where relname = 't_i'
-- result: reltuples | 100
-- and back to 0 again with insert+vacuum
insert into t values(102);
vacuum (verbose off, analyze on, index_cleanup on) t;
select reltuples from pg_class where relname = 't_i'
-- result: reltuples | 0
Before 0d861bbb70, btvacuumpage was adding to relation stats the number of
leaving lines in the block using:
stats->num_index_tuples += maxoff - minoff + 1;
After 0d861bbb70, it is set using new variable nhtidslive:
stats->num_index_tuples += nhtidslive
However, nhtidslive is only incremented if callback (IndexBulkDeleteCallback)
is set, which seems not to be the case on select-only workload.
A naive fix might be to use "maxoff - minoff + 1" when callback==NULL.
Thoughts?
Regards,
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2020-10-23 16:03:19 | Re: Deleting older versions in unique indexes to avoid page splits |
Previous Message | Stephen Frost | 2020-10-23 15:21:49 | Re: [doc] remove reference to pg_dump pre-8.1 switch behaviour |