Queries úteis do PostgreSQL

Listar todas as tabelas, ordenadas por contagem de registros

-- Esta query usa as estatísticas do PostgreSQL para contar.
-- Para números mais precisos, execute
--    VACUUM ANALYZE
-- antes da query.
SELECT
   relname,
   n_live_tup
FROM
   pg_stat_user_tables
ORDER BY
   n_live_tup DESC;

Mostrar todos os índices do banco de dados

Esta query gera comandos create index para todos os índices do banco de dados.

select
   'CREATE INDEX '|| index_name || ' ON ' || table_name || ' ( ' || colunas || ' );'
from
   (select
      table_name,
      index_name,
      array_to_string ( ARRAY (  select
         a.attname as column_name
      from
         pg_class t,
         pg_class i,
         pg_index ix,
         pg_attribute a
      where
         t.oid = ix.indrelid
         and i.oid = ix.indexrelid
         and a.attrelid = t.oid
         and a.attnum = ANY(ix.indkey)
         and t.relkind = 'r'
         and i.relname not like 'fk_%'
         and i.relname not like 'pk_%'
         and i.relname not like '%_pkey'
         and t.relname = base.table_name
         and i.relname = base.index_name
      order by
         t.relname,
         i.relname,
         a.attname ),
      ', ') AS colunas
   from
      (select
         distinct t.relname as table_name,
         i.relname as index_name
      from
         pg_class t,
         pg_class i,
         pg_index ix,
         pg_attribute a
      where
         t.oid = ix.indrelid
         and i.oid = ix.indexrelid
         and a.attrelid = t.oid
         and a.attnum = ANY(
            ix.indkey
         )
         and t.relkind = 'r'
         and i.relname not like 'fk_%'
         and i.relname not like 'pk_%'
         and i.relname not like '%_pkey'
      order by
         t.relname,
         i.relname ) as base) as base2;

Adicionar quebra de linha

SELECT 'First Line' || Chr(13) ||'Second Line';

CHAR(13) é o mesmo que CR. Para seguir a quebra de linha CRLF do Windows, você precisa de CHAR(13)+CHAR(10).

E se a quebra de linha não aparecer?

Ao visualizar usando o psql, ele mostra o \r, que é a representação do caractere de quebra de linha:

Quando visualizamos pelo pgAdmin, a quebra é feita, como deveria ser:

E quanto à minha aplicação?

Se você usar essa dica em uma aplicação desenvolvida por você, será necessário tratar o resultado da query e exibir os caracteres de quebra de linha da forma que desejar. Por exemplo, se você está desenvolvendo uma aplicação web, pode, na camada de visualização, substituir as quebras de linha por <br/>.

Espaço em disco

Tamanho da relação, somente dados

SELECT pg_size_pretty(pg_relation_size('actor'));

Tamanho da relação, com índices

SELECT pg_size_pretty(pg_total_relation_size('actor'));

As 20 maiores tabelas

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Use `any(array)` em vez de `IN`

Usando regex: 8 segundos em ~3,5 milhões de registros

mydatabase=> SELECT my_column FROM my_table WHERE my_column ~* '^12025550188|^12025550103|^12025550193|^12025550143|^12025550141';
-[ RECORD 1 ]--------------
my_column | 12025550188
-[ RECORD 2 ]--------------
my_column | 12025550103
-[ RECORD 3 ]--------------
my_column | 12025550193
-[ RECORD 4 ]--------------
my_column | 12025550143
-[ RECORD 5 ]--------------
my_column | 12025550141

Time: 8060,375 ms (00:08,060)

Usando any: 3 segundos em ~3,5 milhões de registros

mydatabase=> SELECT my_column FROM my_table WHERE my_column LIKE ANY(array['12025550188%','12025550103%','12025550193%','12025550143%','12025550141%']);
-[ RECORD 1 ]--------------
my_column | 12025550188
-[ RECORD 2 ]--------------
my_column | 12025550103
-[ RECORD 3 ]--------------
my_column | 12025550193
-[ RECORD 4 ]--------------
my_column | 12025550143
-[ RECORD 5 ]--------------
my_column | 12025550141

Time: 3712,223 ms (00:03,712)

Referência: Row and Array Comparisons