1、查看表的索引以及索引的列信息

SELECT t.tablename, i.indexname, indisunique, indisprimary, indexdef, ai.attname, ai.attnum, ai.*
  FROM pg_tables t
  JOIN pg_namespace n
    ON t.schemaname = n.nspname
  LEFT JOIN pg_indexes i
    ON t.schemaname = i.schemaname
   AND t.tablename = i.tablename
  LEFT JOIN pg_class ci
    ON i.indexname = ci.relname
   AND n.oid = ci.relnamespace
  LEFT JOIN pg_index ii
    ON ci.oid = ii.indexrelid
  LEFT JOIN pg_attribute ai
    ON ci.oid = ai.attrelid
WHERE t.schemaname = 'xxx'
   AND t.tablename LIKE 'xxx'
ORDER BY t.tablename, i.indexname, ai.attname, ai.attnum;

2、查看表的索、对应列在一起

 SELECT t.tablename,
        i.indexname,
        indisunique,
        indisprimary,
        indexdef,
        substring(indexdef,
                  strpos(indexdef, '(') + 1,
                  strpos(indexdef, ')') - strpos(indexdef, '(') - 1) cols
   FROM pg_tables t
   JOIN pg_namespace n
     ON t.schemaname = n.nspname
   LEFT JOIN pg_indexes i
     ON t.schemaname = i.schemaname
    AND t.tablename = i.tablename
   LEFT JOIN pg_class ci
     ON i.indexname = ci.relname
    AND n.oid = ci.relnamespace
   LEFT JOIN pg_index ii
     ON ci.oid = ii.indexrelid
  WHERE t.schemaname = 'xxx'
    AND t.tablename LIKE 'xxx'
  ORDER BY t.tablename, i.indexname;

3、表注释查看

SELECT nspname, c.relname, d.description
  FROM pg_namespace s
  JOIN pg_class c
    ON s.oid = c.relnamespace
  LEFT JOIN pg_description d
    ON d.objoid = c.oid
   AND d.objsubid = '0'
 WHERE nspname IN ('schema')
   AND c.relkind = 'r'
 ORDER BY nspname, c.relname, d.description;

4、列注释查看

SELECT nspname, c.relname, d.description
  FROM pg_namespace s
  JOIN pg_class c
    ON s.oid = c.relnamespace
  LEFT JOIN pg_description d
    ON d.objoid = c.oid
   AND d.objsubid = '0'
 WHERE nspname IN ('schema')
   AND c.relkind = 'r'
 ORDER BY nspname, c.relname, d.description;

5、重要伪列或系统列

--重要伪列或系统列
--ctid:块编号（通常8k），块内行偏移量
--xc_node_hash：分布式集群的节点hash信息
--xmin：表示插入该行的事务ID（xid）
--xmax：表示更新或删除该行的事务ID（要在MVCC下理解，如果不为0，说明当前的tuple正在被删除或更新，只是事务还没有提交，如果已经提交完，这个版本的tuple就查询不到了）
--xmin、xmax是判断事务可见性、事务隔离的关键列，xid是递增的整数，用于唯一标识
--tableoid：表的oid
select ctid,xc_node_hash,xmin,xmax,tableoid,* from htap_test1 where id >(8000000-1) ;
select txid_current();--获取当前事务id