On Mon, Jan 16, 2023 at 10:28 AM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Sunday, January 15, 2023, jian he <jian.universality@xxxxxxxxx> 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.
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.
