List Append Only / Append Organized table details in Greenplum

posted Apr 28, 2017, 8:02 AM by Sachchida Ojha
sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace AND a.compresstype IS NULL ;
 schema_name | table_name 
-------------+------------
 public      | bar
 public      | bar1
(2 rows)

sachi=# \d bar
Append-Only Table "public.bar"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (a)
sachi=# \d bar1
Append-Only Table "public.bar1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (a)

sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace ;
 schema_name | table_name 
-------------+------------
 public      | bar
 public      | bar1
 public      | foo
 public      | sales
 public      | xyz
(5 rows)
sachi=# \d foo
Append-Only Table "public.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Distributed by: (a)

sachi=# \d sales
Append-Only Columnar Table "public.sales"
 Column |     Type      | Modifiers 
--------+---------------+-----------
 id     | integer       | 
 date   | date          | 
 amt    | numeric(10,2) | 
Checksum: t
Distributed by: (id)

sachi=# \d xyz
       Append-Only Columnar Table "public.xyz"
  Column   |            Type             | Modifiers 
-----------+-----------------------------+-----------
 date_hour | timestamp without time zone | 
 mv        | character varying(255)      | 
 visits    | numeric                     | 
Checksum: t
Distributed by: (date_hour, mv, visits)

sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name, a.compresstype FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace ;
 schema_name | table_name | compresstype 
-------------+------------+--------------
 public      | bar        | 
 public      | bar1       | 
 public      | foo        | zlib
 public      | sales      | quicklz
 public      | xyz        | quicklz
(5 rows)

sachi=# \d pg_catalog.pg_appendonly
    Table "pg_catalog.pg_appendonly"
     Column      |   Type   | Modifiers 
-----------------+----------+-----------
 relid           | oid      | not null
 blocksize       | integer  | not null
 safefswritesize | integer  | not null
 compresslevel   | smallint | not null
 majorversion    | smallint | not null
 minorversion    | smallint | not null
 checksum        | boolean  | not null
 compresstype    | text     | 
 columnstore     | boolean  | 
 segrelid        | oid      | 
 segidxid        | oid      | 
 blkdirrelid     | oid      | 
 blkdiridxid     | oid      | 
 version         | integer  | 
 visimaprelid    | oid      | 
 visimapidxid    | oid      | 
Indexes:
    "pg_appendonly_relid_index" UNIQUE, btree (relid)
sachi=# \d pg_catalog.pg_class                                                                                                                                    
Table "pg_catalog.pg_class"
     Column     |   Type    | Modifiers 
----------------+-----------+-----------
 relname        | name      | not null
 relnamespace   | oid       | not null
 reltype        | oid       | not null
 relowner       | oid       | not null
 relam          | oid       | not null
 relfilenode    | oid       | not null
 reltablespace  | oid       | not null
 relpages       | integer   | not null
 reltuples      | real      | not null
 reltoastrelid  | oid       | not null
 reltoastidxid  | oid       | not null
 relaosegrelid  | oid       | not null
 relaosegidxid  | oid       | not null
 relhasindex    | boolean   | not null
 relisshared    | boolean   | not null
 relkind        | "char"    | not null
 relstorage     | "char"    | not null
 relnatts       | smallint  | not null
 relchecks      | smallint  | not null
 reltriggers    | smallint  | not null
 relukeys       | smallint  | not null
 relfkeys       | smallint  | not null
 relrefs        | smallint  | not null
 relhasoids     | boolean   | not null
 relhaspkey     | boolean   | not null
 relhasrules    | boolean   | not null
 relhassubclass | boolean   | not null
 relfrozenxid   | xid       | not null
 relacl         | aclitem[] | 
 reloptions     | text[]    | 
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name, c.reltype,c.relowner,c.relpages, c.relkind,c.relstorage,c.relhasindex,a.compresstype, a.compresslevel,a.columnstore FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace ;
 schema_name | table_name | reltype | relowner | relpages | relkind | relstorage | relhasindex | compresstype | compresslevel | columnstore 
-------------+------------+---------+----------+----------+---------+------------+-------------+--------------+---------------+-------------
 public      | bar        |   37322 |    17146 |        0 | r       | a          | f           |              |             0 | f
 public      | bar1       |   37354 |    17146 |        0 | r       | a          | f           |              |             0 | f
 public      | foo        |   37386 |    17146 |        0 | r       | a          | f           | zlib         |             5 | f
 public      | sales      |   37446 |    17146 |        1 | r       | c          | f           | quicklz      |             1 | t
 public      | xyz        |   37476 |    17146 |        0 | r       | c          | f           | quicklz      |             1 | t
(5 rows)

sachi=# 
Comments