Pivotal Knowledge Base

Follow

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

Environment

Product Version
Pivotal Greenplum All Versions

Problem

Querying the table "pg_class" for column relkind to know the types of the object, shows value "b" for some relname.

Solution

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 a 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
  • Where 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

Comments

Powered by Zendesk