On Mon, Jan 16, 2023 at 10:28 AM David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Sunday, January 15, 2023, jian he <jian.universal...@gmail.com> wrote: > >> >> >> Hi, >> why the materialized view itself bloats. If no refresh then no bloat >> right? If fresh then set based delete operation will make materialized view >> bloat? >> I also found the same question online. >> https://dba.stackexchange.com/questions/219079/bloat-on-materialized-views >> Unfortunately nobody answered... >> >> > The definition of bloat is a deleted row. Bloat can be reduced by > subsequent row insertions. > > David J. > > Hi. In the following example, I cannot see bloat (via extension pgstattuple dead_tuple_count>0). Wondering where the bloat is. BEGIN;create table tbt( a int) with(fillfactor=40, autovacuum_enabled=off); insert into tbt select g from generate_series(1,2000) g; create materialized view tbtmv as select * from tbt; commit; --------do the update. update tbt set a = 10 + a where a < 20; REFRESH MATERIALIZED view tbtmv; SELECT * FROM pgstattuple('tbtmv'); -----no dead tuples count. ------try delete. delete from tbt where a < 50; REFRESH MATERIALIZED view tbtmv; SELECT * FROM pgstattuple('tbtmv');-------still no dead tuples.
