Início / dba / Perguntas / 345321
Accepted
Chris Douglas
Asked: 2025-02-17 18:01:53 +0800 CST

Adicionar coluna de contagem de linhas à consulta de tamanho de tabela

  • 772

Tenho o seguinte SQL que gera uma lista bem formatada dos tamanhos de todas as tabelas em um banco de dados:

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size
FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

Isso gera dados como os seguintes (alguns nomes e tamanhos foram alterados para ocultar dados, mas você entendeu a ideia):

     schema_name     |                 relname                 |    size
---------------------+-----------------------------------------+------------
 information_schema  | log                                     | 1024 kB
 information_schema  | collations                              | 1024 kB
 information_schema  | routines                                | 1024 kB
(30 rows)

Gostaria de ter os resultados de count() em cada tabela adicionados como uma quarta coluna aqui, mas não consigo descobrir como fazer isso. Estou usando o PostgreSQL 14, se isso especificamente fizer alguma diferença.

postgresql

1 respostas

  1. Best Answer

    Você pode fazer isso (db<>fiddle aqui ):

    SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
    
  estimate AS row_count_est     --   <<  Note!

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname        AS schema_name,

         pg_catalog.pg_class.reltuples::BIGINT  AS estimate,     --  <<   Note!
         
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

    Resultado (de db<>fiddle> - recortado para abreviar):

    schema_name          relname                   size        row_count_est
information_schema   sql_features              64 kB       755
information_schema   sql_sizing                8192 bytes  23
information_schema   sql_implementation_info   8192 bytes  12
information_schema   sql_parts                 8192 bytes  11
information_schema   administrable_role_...    0 bytes     -1
...
...
...

    Observe o registro final com uma estimativa de -1 - isso significa que a tabela nunca foi analisada pelo sistema e, portanto, ele não sabe qual é a contagem e não quer fornecer nem mesmo uma estimativa.

    Você pode obter vários graus de precisão na resposta de Erwin Brandstetter aqui , mas esteja ciente de que respostas mais precisas exigem mais recursos do sistema para fornecer essa resposta - devido ao sistema MVCC do PostgreSQL, contagens precisas são mais difíceis do que com o MySQL, por exemplo, que usa um mecanismo diferente.

    A importância de ANALYZE-ing pode ser vista neste db<>fiddle onde a estimativa é -1 antes da análise e (o número correto) 100.000 depois - mostra o quão importante é que as estatísticas do sistema sejam mantidas atualizadas!

    • 4

relate perguntas