know where compression applied in greenplum postgresql table

Question

I have a order table which has 50 columns in my greenplum postgres database. Which has billions of data in the table but when I run \dt+ order I am getting just 27MB size and I have distributed on order_id column. please help me to find out where the compression is being applied.

\dt+ order the above command returns 27MB

I want know where the compression is applied

Answer 1

Given a table named edw.oak, When you run \dt+ oak from psql on Greenplum 6.x, the following query is run

2023-09-27 11:45:21.013536 CDT,"gpadmin","ww_sales",p25178,th-1510041472,"[local]",,2023-09-27 11:44:32 CDT,0,con18774,cmd10,seg-1,,,,sx1,"LOG","00000","statement: SELECT n.nspname as ""Schema"",
  c.relname as ""Name"",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as ""Type"",
  pg_catalog.pg_get_userbyid(c.relowner) as ""Owner"", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' WHEN 'p' THEN 'Apache Parquet' WHEN 'f' THEN 'foreign' END as ""Storage""
,
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as ""Size"",
  pg_catalog.obj_description(c.oid, 'pg_class') as ""Description""
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND c.relstorage IN ('h', 'a', 'c','')
      AND n.nspname !~ '^pg_toast'
  AND c.relname OPERATOR(pg_catalog.~) '^(oak)$'
  AND n.nspname OPERATOR(pg_catalog.~) '^(edw)$'
ORDER BY 1,2;",,,,,,"SELECT n.nspname as ""Schema"",
  c.relname as ""Name"",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as ""Type"",
  pg_catalog.pg_get_userbyid(c.relowner) as ""Owner"", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' WHEN 'p' THEN 'Apache Parquet' WHEN 'f' THEN 'foreign' END as ""Storage""
,
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as ""Size"",
  pg_catalog.obj_description(c.oid, 'pg_class') as ""Description""
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND c.relstorage IN ('h', 'a', 'c','')
      AND n.nspname !~ '^pg_toast'
  AND c.relname OPERATOR(pg_catalog.~) '^(oak)$'
  AND n.nspname OPERATOR(pg_catalog.~) '^(edw)$'
ORDER BY 1,2;",0,,"postgres.c",1667,

So the "Size" column is determined by pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as ""Size""

What type of storage is your table using? Heap? AO?

Can you provide the full DDL of the table?

What is the output of this command:

ww_sales=# show gp_default_storage_options;
                            gp_default_storage_options
----------------------------------------------------------------------------------
 appendonly=false,blocksize=32768,compresstype=none,checksum=true,orientation=row
(1 row)

ww_sales=#