Understanding Greenplum Bloat on heap, AO and AO columnar tables by examples  and how to remove it.

Post date: Apr 22, 2022 10:14:59 PM

Step 1: Create heap, ao and ao columnar (column oriented) table 

drop table IF EXISTS sachi.vac_test_heap; 

drop table IF EXISTS sachi.vac_test_ao; 

drop table IF EXISTS sachi.vac_test_aoco; 

create table 

sachi.vac_test_heap( 

a int,  

b text)  

distributed by (a); 

create table 

sachi.vac_test_ao( 

a int,  

b text 

)  

with (appendonly=true)  

distributed by (a); 

create table 

sachi.vac_test_aoco( 

a int,  

b text 

)  

with (appendonly=true, orientation=column)  

distributed by (a); 

DROP TABLE 

DROP TABLE 

DROP TABLE 

CREATE TABLE 

CREATE TABLE 

CREATE TABLE 

select 

c.oid, 

c.relname, 

n.nspname, 

c.relkind, 

c.reltuples, 

c.relstorage 

 from pg_class c  

 JOIN pg_namespace n ON c.relnamespace = n.oid  

 where n.nspname='sachi' 

 and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

oid | relname | nspname | relkind | reltuples | relstorage

----------+---------------+---------+---------+-----------+------------

19807184 | vac_test_heap | sachi | r | 0 | h

19807190 | vac_test_ao | sachi | r | 0 | a

19807200 | vac_test_aoco | sachi | r | 0 | c

(3 rows)

SELECT 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows FROM pg_appendonly a 

JOIN pg_class c ON c.oid = a.relid 

JOIN pg_namespace n ON c.relnamespace = n.oid 

WHERE c.relkind = 'r' 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

schema_name | table_name | num_rows | ao_num_rows 

-------------+------------+----------+------------- 

(0 rows) 

insert into vac_test_heap values (generate_series(1,10000000),'this is a test');

insert into vac_test_ao values (generate_series(1,10000000),'this is a test');

insert into vac_test_aoco values (generate_series(1,10000000),'this is a test'); 

oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

----------+-------------+---------------+----------+-------------+--------+---------- +-------- 

19807190 | sachi | vac_test_ao | 0 | 10000000 | 0.00 | 10000000  | 0 

19807200 | sachi | vac_test_aoco | 0 | 10000000 | 0.00 | 10000000  | 0 

(2 rows) 

-- Now run analyze on each table. 

analyze sachi.vac_test_heap; 

analyze sachi.vac_test_ao; 

analyze sachi.vac_test_aoco; 

ANALYZE 

ANALYZE 

ANALYZE  

select c.oid,c.relname,n.nspname,c.relkind,c.relpages,  

c.reltuples::bigint,c.relstorage 

from pg_class c 

JOIN pg_namespace n ON c.relnamespace = n.oid 

where n.nspname='sachi' and c.relname in 

('vac_test_heap','vac_test_ao','vac_test_aoco'); 

oid | relname | nspname | relkind | relpages | reltuples | relstorage

----------+---------------+---------+---------+----------+-----------+------------

19807184 | vac_test_heap | sachi | r | 16000 | 10011500 | h

19807190 | vac_test_ao | sachi | r | 10496 | 10000000 | a

19807200 | vac_test_aoco | sachi | r | 5888 | 10000000 | c (3 rows) 

select count(*) from sachi.vac_test_heap; 

count 

---------- 

10000000 

(1 row) 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows, (SELECT max(percent_hidden) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as perhid, 

(SELECT sum(total_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as tottup, 

(SELECT max(hidden_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid))  as hidtup 

FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

----------+-------------+---------------+----------+-------------+--------+---------- +-------- 

19807190 | sachi | vac_test_ao | 10000000 | 10000000 | 0.00 | 10000000  | 0 

19807200 | sachi | vac_test_aoco | 10000000 | 10000000 | 0.00 | 10000000  | 0 

(2 rows) 

insert into vac_test_heap values (generate_series(1,500000),'this is a new 5 percent  rows test'); 

insert into vac_test_ao values (generate_series(1,500000),'this is a new 5 percent  rows test'); 

insert into vac_test_aoco values (generate_series(1,500000),'this is a new 5 percent  rows test'); 

oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

----------+-------------+---------------+----------+-------------+--------+---------- +-------- 

19807190 | sachi | vac_test_ao | 10000000 | 10500000 | 0.00 | 10500000 | 0 

19807200 | sachi | vac_test_aoco | 10000000 | 10500000 | 0.00 | 10500000 | 0 

(2 rows) 

select 

oid, 

schema_name, 

table_name, 

num_rows, 

ao_num_rows, 

(sum(ao_num_rows)/num_rows) as pct 

from 

( 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows,

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

AND c.reltuples > 0 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco') 

--order by 4 desc limit 50000 

) 

as sub 

GROUP BY 1,2,3,4,5 

HAVING sum(ao_num_rows) > num_rows * (1::numeric + (5::numeric/100::numeric)) order by 4 desc; 

oid | schema_name | table_name | num_rows | ao_num_rows | pct 

-----+-------------+------------+----------+-------------+----- 

(0 rows) 

insert into vac_test_heap values (generate_series(1,5000),'this is a new 1 percent  rows test'); 

insert into vac_test_ao values (generate_series(1,5000),'this is a new 1 percent rows  testt'); 

insert into vac_test_aoco values (generate_series(1,5000),'this is a new 1 percent  rows test'); 

INSERT 0 5000 

INSERT 0 5000 

INSERT 0 5000 

select 

oid, 

schema_name, 

table_name, 

num_rows, 

ao_num_rows, 

(sum(ao_num_rows)/num_rows) as pct 

from 

( 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

AND c.reltuples > 0 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco') 

--order by 4 desc limit 50000 

) 

as sub 

GROUP BY 1,2,3,4,5

HAVING sum(ao_num_rows) > num_rows * (1::numeric + (5::numeric/100::numeric)) order by 4 desc; 

 oid | schema_name | table_name | num_rows | ao_num_rows | pct

----------+-------------+---------------+----------+-------------+------------------- - 

19807190 | sachi | vac_test_ao | 10000000 | 10505000 | 1.0505000000000000

19807200 | sachi | vac_test_aoco | 10000000 | 10505000 | 1.0505000000000000

(2 rows) 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows, (SELECT max(percent_hidden) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as perhid, 

(SELECT sum(total_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as tottup, 

(SELECT max(hidden_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid))  as hidtup 

INSERT 0 10000000 

INSERT 0 10000000 

INSERT 0 10000000 

select c.oid,c.relname,n.nspname,c.relkind,c.relpages,  

c.reltuples::bigint,c.relstorage 

from pg_class c 

JOIN pg_namespace n ON c.relnamespace = n.oid 

where n.nspname='sachi' and c.relname in 

('vac_test_heap','vac_test_ao','vac_test_aoco'); 

oid | relname | nspname | relkind | relpages | reltuples | relstorage

----------+---------------+---------+---------+----------+-----------+------------

19807184 | vac_test_heap | sachi | r | 16000 | 10011500 | h

19807190 | vac_test_ao | sachi | r | 10496 | 10000000 | a

19807200 | vac_test_aoco | sachi | r | 5888 | 10000000 | c

(3 rows) 

analyze sachi.vac_test_heap; 

analyze sachi.vac_test_ao; 

analyze sachi.vac_test_aoco; 

select c.oid,c.relname,n.nspname,c.relkind,c.relpages,  

c.reltuples::bigint,c.relstorage 

from pg_class c 

JOIN pg_namespace n ON c.relnamespace = n.oid 

where n.nspname='sachi' and c.relname in 

('vac_test_heap','vac_test_ao','vac_test_aoco'); 

delete from sachi.vac_test_heap where a<800000; 

delete from sachi.vac_test_ao where a<800000; 

delete from sachi.vac_test_aoco where a<800000; 

analyze sachi.vac_test_heap; 

analyze sachi.vac_test_ao; 

analyze sachi.vac_test_aoco; 

DELETE 14400000 

DELETE 14400000 

DELETE 14400000 

ANALYZE 

ANALYZE 

ANALYZE 

 ----no Bloat 

select bdinspname as "Schema Name", 

bdirelname as "Table/Index Name", 

bdirelpages as "Pages(current)",bdiexppages as "Pages(Expected)", bdidiag as "Comments" 

from gp_toolkit.gp_bloat_diag where bdinspname='sachi' and bdirelname in ('vac_test_heap','vac_test_ao','vac_test_aoco') ; 

Schema Name | Table/Index Name | Pages(current) | Pages(Expected) | Comments

-------------+------------------+----------------+-----------------+----------

(0 rows) 

analyze sachi.vac_test_heap; 

analyze sachi.vac_test_ao; 

analyze sachi.vac_test_aoco; 

select bdinspname as "Schema Name", 

bdirelname as "Table/Index Name", 

bdirelpages as "Pages(current)",bdiexppages as "Pages(Expected)", bdidiag as "Comments" 

from gp_toolkit.gp_bloat_diag where bdinspname='sachi' and bdirelname in ('vac_test_heap','vac_test_ao','vac_test_aoco') ; 

Schema Name | Table/Index Name | Pages(current) | Pages(Expected) |  Comments 

-------------+------------------+----------------+-----------------+----------------- ------------------- 

sachi | vac_test_heap | 32896 | 5086 | moderate amount  of bloat suspected 

(1 row) 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows, (SELECT max(percent_hidden) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as perhid, 

(SELECT sum(total_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as tottup, 

(SELECT max(hidden_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid))  as hidtup 

FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

 oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

----------+-------------+---------------+----------+-------------+--------+---------- +-------- 

19807190 | sachi | vac_test_ao | 4000002 | 20505000 | 80.49 | 20505000  | 128947 

19807200 | sachi | vac_test_aoco | 4000002 | 20505000 | 80.49 | 20505000  | 128947 

(2 rows) 

vacuum sachi.vac_test_heap; 

vacuum sachi.vac_test_ao; 

vacuum sachi.vac_test_aoco;

VACUUM 

VACUUM 

VACUUM 

 -- Still show bloat on heap tables.  

select bdinspname as "Schema Name", 

bdirelname as "Table/Index Name", 

bdirelpages as "Pages(current)",bdiexppages as "Pages(Expected)", bdidiag as "Comments" 

from gp_toolkit.gp_bloat_diag where bdinspname='sachi' and bdirelname in ('vac_test_heap','vac_test_ao','vac_test_aoco') ; 

Schema Name | Table/Index Name | Pages(current) | Pages(Expected) |  Comments 

-------------+------------------+----------------+-----------------+----------------- ------------------- 

sachi | vac_test_heap | 32896 | 5372 | moderate amount  of bloat suspected 

(1 row) 

-- Hidden tuple is gone but see additional rows. 

 SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows, 

(SELECT max(percent_hidden) FROM 

gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as perhid, 

(SELECT sum(total_tupcount) FROM 

gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as tottup, 

(SELECT max(hidden_tupcount) FROM 

gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as hidtup 

FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

and n.nspname='sachi' 

 and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

 oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

(4 rows) 

vacuum full sachi.vac_test_heap; 

vacuum full sachi.vac_test_ao; 

vacuum full sachi.vac_test_aoco; 

VACUUM 

VACUUM 

VACUUM 

select bdinspname as "Schema Name", 

bdirelname as "Table/Index Name", 

bdirelpages as "Pages(current)",bdiexppages as "Pages(Expected)", bdidiag as "Comments" 

from gp_toolkit.gp_bloat_diag where bdinspname='sachi' and bdirelname in ('vac_test_heap','vac_test_ao','vac_test_aoco') ; 

Schema Name | Table/Index Name | Pages(current) | Pages(Expected) | Comments

-------------+------------------+----------------+-----------------+----------

(0 rows) 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows, (SELECT max(percent_hidden) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as perhid, 

(SELECT sum(total_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as tottup, 

(SELECT max(hidden_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid))  as hidtup 

FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

----------+-------------+---------------+----------+-------------+--------+--------- +-------- 

19807190 | sachi | vac_test_ao | 4000002 | 0 | 0.00 | 4000002 |  0 

19807190 | sachi | vac_test_ao | 4000002 | 4000002 | 0.00 | 4000002 |  0 

19807200 | sachi | vac_test_aoco | 4000002 | 0 | 0.00 | 4000002 |  0 

19807200 | sachi | vac_test_aoco | 4000002 | 4000002 | 0.00 | 4000002 |  0 

(4 rows) 

analyze sachi.vac_test_heap; 

analyze sachi.vac_test_ao; 

analyze sachi.vac_test_aoco; 

ANALYZE

ANALYZE 

ANALYZE 

 select bdinspname as "Schema Name", 

bdirelname as "Table/Index Name", 

bdirelpages as "Pages(current)",bdiexppages as "Pages(Expected)", bdidiag as "Comments" 

from gp_toolkit.gp_bloat_diag where bdinspname='sachi' and bdirelname in ('vac_test_heap','vac_test_ao','vac_test_aoco') ; 

Schema Name | Table/Index Name | Pages(current) | Pages(Expected) | Comments

-------------+------------------+----------------+-----------------+----------

(0 rows) 

SELECT 

c.oid, 

n.nspname AS schema_name, 

c.relname AS table_name, 

c.reltuples::bigint AS num_rows, 

(gp_toolkit.__gp_aovisimap_hidden_info(c.oid)).total_tupcount AS ao_num_rows, (SELECT max(percent_hidden) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as perhid, 

(SELECT sum(total_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid)) as tottup, 

(SELECT max(hidden_tupcount) FROM gp_toolkit.__gp_aovisimap_compaction_info(c.oid))  as hidtup 

FROM pg_appendonly a 

JOIN pg_class c ON c.oid=a.relid 

JOIN pg_namespace n ON c.relnamespace=n.oid 

WHERE c.relkind='r' 

and n.nspname='sachi' 

and c.relname in ('vac_test_heap','vac_test_ao','vac_test_aoco'); 

 oid | schema_name | table_name | num_rows | ao_num_rows | perhid | tottup |  hidtup 

19807200 | sachi | vac_test_aoco | 4000002 | 4000002 | 0.00 | 4000002 |  0 

(4 rows) 

Get more information 

SELECT * FROM gp_toolkit.__gp_aovisimap_compaction_info(19807190);

SELECT * FROM gp_toolkit.__gp_aovisimap_compaction_info(19807200);

SELECT * FROM gp_toolkit.__gp_aovisimap_hidden_info(19807200); 

SELECT * FROM gp_toolkit.__gp_aovisimap_hidden_info(19807190);