List Append Only / Append Organized table details in Greenplum

Post date: May 27, 2015 10:12:34 PM

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