Understanding GPDB BLOAT on heap, AO, AO Columnar tables by examples

posted Apr 22, 2022, 3:08 PM by Sachchida Ojha

Understanding Greenplum Bloat by Examples    

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')

INSERT 0 10000000 

INSERT 0 10000000 

INSERT 0 10000000 

  

select c.oid,c.relname,n.nspname,c.relkind,c.relpages, 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 | relpages | reltuples | relstorage ----------+---------------+---------+---------+----------+-----------+------------ 19807184 | vac_test_heap | sachi | r | 0 | 0 | h 19807190 | vac_test_ao | sachi | r | 0 | 0 | a 19807200 | vac_test_aoco | sachi | r | 0 | 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 

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

sachi | vac_test_ao | 0 | 10000000 

sachi | vac_test_aoco | 0 | 10000000 

(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 

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 | 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')

INSERT 0 500000 

INSERT 0 500000 

INSERT 0 500000 

 

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 

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 | 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 >

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,

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 >

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 

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 | 10505000 | 0.00 | 10505000  | 0 

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

(2 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')

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 -------------+------------------+----------------+-----------------+---------- (0 rows) 

 

delete from sachi.vac_test_heap where a<8000000

delete from sachi.vac_test_ao where a<8000000

delete from sachi.vac_test_aoco where a<8000000

  

analyze sachi.vac_test_heap

analyze sachi.vac_test_ao

analyze sachi.vac_test_aoco

  

-- Now bloat diag views shows the bloat. AO and AOCO Table does not from this view. 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 

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

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) 

  

 

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 

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

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) 

  

  

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);


Comments