Início / dba / Perguntas / 345152
Accepted
Michal Charemza
Asked: 2025-02-07 16:42:02 +0800 CST

Como posso listar as tabelas em um esquema sem uma varredura sequencial de pg_class?

  • 772

No PostgreSQL, eu gostaria de listar as tabelas em um esquema. Eu tenho essa consulta que faz isso para o esquema público, por exemplo:

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

No entanto, no meu sistema isso leva ~400-500ms (e às vezes pode levar muito mais tempo). Suspeito que isso seja porque pg_class tem mais de 5 milhões de linhas e tem quase 5gb de tamanho.

Olhando para a explicação e análise da consulta, vejo isto:

                                                        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

e vejo que tem uma varredura sequencial em pg_class. Olhando para os índices em pg_class, isso não é surpreendente - não há índice na coluna relnamespace (pelo menos, nenhum índice que tenha relnamespace como sua primeira coluna).

Então, como posso evitar a varredura sequencial em pg_class ao tentar encontrar as tabelas em um esquema? Isso é possível?

postgresql

1 respostas

  1. Best Answer

    Encontrei uma maneira que aproveita a tabela pg_depend e o fato de que uma entrada em pg_class sempre vem com uma linha em pg_depend, vinculando-a ao seu esquema.

    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';

    Agora, isso é executado em < 3 ms no meu sistema, sem varreduras sequenciais em nenhuma tabela e uma ANÁLISE EXPLAIN de:

                                                                      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

    E o acima pode ser ajustado dependendo de suas necessidades. Por exemplo, se você quiser incluir também visualizações, visualizações materializadas, tabelas particionadas (mas não suas partições individuais), você pode fazer isso:

    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;

    Ou talvez você tenha uma lista de esquemas e queira as tabelas não particionadas, visualizações e visualizações materializadas em todas elas

    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

relate perguntas