What are the objects with relkind="b" under pg_class?

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

This article applies to Pivotal Greenplum Database (GPDB) all versions.
Querying the table "pg_class" for column relkind to know the types of the object, shows value "b" for some relname.

 

Environment

Resolution

The relkind="b" is a index which is created when you create a user defined index on top of the parent table which is also a append-only table.

For better understanding have a look at a quick example:

• Current pg_class with relkind=b

  gpadmin=# select * from pg_class where relkind='b';
   relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrelid | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions 
  ---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
  (0 rows)
  
  Time: 26.847 ms

• Create a table with "appendonly".

  gpadmin=# create table test ( a int ) WITH (appendonly=true);
  NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as GPDB data distribution key for this table.
  HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
  CREATE TABLE
  Time: 359.840 ms
  gpadmin=#

• Create an index on top of it.

  gpadmin=# create index test_idx on test(a);
  CREATE INDEX
  Time: 312.075 ms

• Now, the value with relkind=b populates under pg_class.

  gpadmin=# select * from pg_class where relkind='b';
        relname       | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrelid | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions 
  --------------------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
   pg_aoblkdir_257585 |         6104 |  257616 |       10 |     0 |      257613 |             0 |        0 |         0 |             0 |             0 |             0 |             0 | t           | f           | b       | h          |        4 |         0 |           0 |        0 |        0 |       0 | f          | t          | f           | f              |        48999 |        | 
  (1 row)
  
  Time: 26.084 ms

• And, there the "pg_aoblkdir_257585" points to its parent table.

  gpadmin=# select oid,relname from pg_class where oid=257585;
    oid   | relname 
  --------+---------
   257585 | test
  (1 row)
  
  Time: 2.375 ms