主页 / dba / 问题 / 345152
Accepted
Michal Charemza
Asked: 2025-02-07 16:42:02 +0800 CST

如何在没有对 pg_class 进行顺序扫描的情况下列出模式中的表？

  • 772

在 PostgreSQL 中，我想列出模式中的表。我有以下针对公共模式执行此操作的查询，例如：

SELECT
  relname
FROM
  pg_class
WHERE
  relnamespace = 'public'::regnamespace
  AND relkind = 'r';

但是，在我的系统上，这需要大约 400-500 毫秒（有时可能需要更长时间）。我怀疑这是因为 pg_class 有超过 500 万行，大小接近 5GB。

查看查询的解释分析，我看到了以下内容：

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..356932.33 rows=14 width=64) (actual time=2.190..418.126 rows=120 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on pg_class  (cost=0.00..355930.92 rows=6 width=64) (actual time=5.318..375.222 rows=40 loops=3)
         Filter: ((relnamespace = '2200'::oid) AND (relkind = 'r'::"char"))
         Rows Removed by Filter: 1792453
 Planning Time: 0.070 ms
 Execution Time: 418.162 ms

我发现它对 pg_class 进行了顺序扫描。查看 pg_class 上的索引，这并不奇怪 - relnamespace 列上没有索引（至少，没有以 relnamespace 作为其第一列的索引）。

那么，在尝试查找模式中的表时，如何避免对 pg_class 进行顺序扫描？这可能吗？

postgresql

1 个回答

  1. Best Answer

    我找到了一种利用 pg_depend 表的方法，并且 pg_class 中的条目始终带有 pg_depend 中的一行，将其链接到其模式。

    SELECT
  objid::regclass
FROM
  pg_depend
INNER JOIN
  pg_class ON pg_class.oid = pg_depend.objid
WHERE
  pg_depend.refobjid = 'public'::regnamespace
  AND pg_depend.refclassid = 'pg_namespace'::regclass
  AND pg_depend.classid = 'pg_class'::regclass
  AND pg_class.relkind = 'r';

    现在，它在我的系统上运行时间不到 3 毫秒，无需对任何表进行连续扫描，并且 EXPLAIN ANALYZE 为：

                                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.99..41.60 rows=1 width=4) (actual time=0.672..2.610 rows=120 loops=1)
   ->  Index Scan using pg_depend_reference_index on pg_depend  (cost=0.56..16.24 rows=3 width=4) (actual time=0.642..1.725 rows=196 loops=1)
         Index Cond: ((refclassid = '2615'::oid) AND (refobjid = '2200'::oid))
         Filter: (classid = '1259'::oid)
         Rows Removed by Filter: 1573
   ->  Index Scan using pg_class_oid_index on pg_class  (cost=0.43..8.45 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=196)
         Index Cond: (oid = pg_depend.objid)
         Filter: (relkind = 'r'::"char")
         Rows Removed by Filter: 0
 Planning Time: 0.296 ms
 Execution Time: 2.661 ms

    以上内容可以根据您的需要进行调整。例如，如果您还想包含视图、物化视图、分区表（但不包含它们各自的分区），您可以这样做：

    SELECT
  objid::regclass
FROM
  pg_depend
INNER JOIN
  pg_class ON pg_class.oid = pg_depend.objid
WHERE
  pg_depend.refobjid = 'public'::regnamespace
  AND pg_depend.refclassid = 'pg_namespace'::regclass
  AND pg_depend.classid = 'pg_class'::regclass
  AND pg_class.relkind = ANY(ARRAY['p', 'r', 'v', 'm'])
  AND NOT pg_class.relispartition;

    或者，也许您有一个架构列表，并且想要其中所有非分区表、视图和物化视图

    SELECT
  objid::regclass
FROM
  pg_depend
INNER JOIN
  pg_class ON pg_class.oid = pg_depend.objid
WHERE
  pg_depend.refobjid = ANY(ARRAY['schema_1'::regnamespace, 'schema_2'::regnamespace, 'schema_3'::regnamespace])
  AND pg_depend.refclassid = 'pg_namespace'::regclass
  AND pg_depend.classid = 'pg_class'::regclass
  AND pg_class.relkind = ANY(ARRAY['p', 'r', 'v', 'm'])
  AND NOT pg_class.relispartition;
    • 1

相关问题