查看表和数据库的信息 本章节主要介绍查看表和数据库信息的最佳实践。 查询表信息 使用系统表pgtables查询数据库所有表的信息。 SELECT FROM pgtables; 使用gsql的d+命令查询表结构。 示例:先创建表customert1并插入数据。 CREATE TABLE customert1 ( ccustomersk integer, ccustomerid char(5), cfirstname char(6), clastname char(8) ) with (orientation column,compressionmiddle) distribute by hash (clastname); INSERT INTO customert1 (ccustomersk, ccustomerid, cfirstname) VALUES (6885, 'map', 'Peter'), (4321, 'river', 'Lily'), (9527, 'world', 'James'); 查询表结构。(若建表时不指定schema,则表的默认schemaname是public) d+ customert1; Table "public.customert1" Column Type Modifiers Storage Stats target Description +++++ ccustomersk integer plain ccustomerid character(5) extended cfirstname character(6) extended clastname character(8) extended Has OIDs: no Distribute By: HASH(clastname) Location Nodes: ALL DATANODES Options: orientationcolumn, compressionmiddle, colversion2.0, enabledeltafalse 使用函数pggettabledef查询表定义。 SELECT FROM PGGETTABLEDEF('customert1'); pggettabledef SET searchpath tpchobs; + CREATE TABLE customert1 ( + ccustomersk integer, + ccustomerid character(5), + cfirstname character(6), + clastname character(8) + ) + WITH (orientationcolumn, compressionmiddle, colversion2.0, enabledeltafalse)+ DISTRIBUTE BY HASH(clastname) + TO GROUP groupversion1; (1 row) 执行如下命令查询表customert1的所有数据。 SELECT FROM customert1; ccustomersk ccustomerid cfirstname clastname +++ 6885 map Peter 4321 river Lily 9527 world James (3 rows) 使用SELECT查询表customert1中某一字段的所有数据。 SELECT ccustomersk FROM customert1; ccustomersk 6885 4321 9527 (3 rows) 查询表是否做过表分析,执行如下命令会返回每个表最近一次做analyze的时间,没有返回的则表示没有做过analyze。 select pgstatgetlastanalyzetime(oid),relname from pgclass where relkind'r'; 查询public下的表做表分析的时间: select pgstatgetlastanalyzetime(c.oid),c.relname from pgclass c left join pgnamespace n on c.relnamespace n.oid where c.relkind'r' and n.nspname'public'; pgstatgetlastanalyzetime relname + 20220517 07:48:26.923782+00 warehouset19 20220517 07:48:26.964512+00 emp 20220517 07:48:27.016709+00 testtriggersrctbl 20220517 07:48:27.045385+00 customer 20220517 07:48:27.062486+00 warehouset1 20220517 07:48:27.114884+00 customert1 20220517 07:48:27.172256+00 productinfoinput 20220517 07:48:27.197014+00 tt1 20220517 07:48:27.212906+00 timezonetest (9 rows)