Obtenha estimativas de contagem de pg_class.reltuples para determinadas condições

Não fora da caixa. Mas você pode conseguir isso com um...

índice parcial

CREATE INDEX tbl_name_hello_idx ON tbl(tbl_id) WHERE name LIKE 'hello%';

SELECT reltuples
FROM   pg_class
WHERE  oid = 'tbl_name_hello_idx'::regclass;  -- or schema-qualify table name

A coluna de índice real ( tbl_idno exemplo) é irrelevante (a menos que você tenha uso adicional para o índice). Melhor escolher uma pequena coluna que nunca é alterada: uma serialcoluna PK seria uma candidata perfeita. Ou você pode usar uma constante (que anula propósitos adicionais desse índice):

CREATE INDEX tbl_name_hello_idx ON tbl((1)) WHERE name LIKE 'hello%';

Cada índice tem sua própria entrada pg_classe sua própria reltuplescontagem. A documentação:

reltuples...
Número de linhas na tabela. Esta é apenas uma estimativa usada pelo planejador. Ele é atualizado por VACUUM, ANALYZEe alguns comandos DDL, como CREATE INDEX.

Portanto, um índice parcial pode ser (ab-)usado para obter estimativas de contagem para qualquer conjunto de predicados, atualizados autovacuumautomaticamente. Ou talvez você já tenha o índice porque precisa dele mesmo assim?

Isso pode realmente ser uma ideia muito inteligente. Mas é preciso pesar custos e benefícios: o índice parcial é pequeno para uma condição rara , mas o custo aumenta para uma condição comum . E embora manter as estatísticas atualizadas seja comparativamente barato, não é gratuito.

Relacionado:

• Índice que não é usado, mas influencia a consulta
• Maneira rápida de descobrir a contagem de linhas de uma tabela

TABLESAMPLE SYSTEM (n)no Postgres 9.5+

Você vai adorar o novo recurso do próximo Postgres 9.5, que analisa apenas uma amostra aleatória de n % de blocos na tabela para obter uma estimativa rápida. Exemplo para 1 %:

SELECT 100 * count(*) AS estimate
FROM   tbl TABLESAMPLE SYSTEM (1)
WHERE  name LIKE 'hello%';

Detalhes na resposta já vinculada acima:

• Maneira rápida de descobrir a contagem de linhas de uma tabela

Alternativa para o exemplo dado

Para o exemplo dado , você pode obter resultados exatosname LIKE 'hello%' muito rápidos usando o índice correto de qualquer maneira:

CREATE INDEX tbl_name_text_pattern_idx ON tbl(name text_pattern_ops);

Ver:

• Como otimizar uma query envolvendo um sort e um LIKE?
• Correspondência de padrões com LIKE, SIMILAR TO ou expressões regulares no PostgreSQL

Você já deu excelentes conselhos sobre como criar índices para obter a estimativa com mais eficiência. Mas se você não quiser criar outro índice para fazer isso, outra abordagem é pedir PostgreSQLpara fazer a estimativa para você.

Se você quiser uma estimativa de uma consulta como:

select count(*) from foo where bar like 'Hello%';

O que você pode fazer é executar:

explain select * from foo where bar like 'Hello%';

A primeira linha retornada por esta consulta será semelhante a:

Seq Scan on foo  (cost=0.00..52853.94 rows=1520803 width=256)

E você pode analisar o número após 'rows='

Esta é a estimativa que PostgreSQLutiliza para o seu próprio planejamento interno, e que incorpora todas as informações que PostgreSQLsabe incorporar.

Claro que você pode usar. Veja esta página no documento do postgresql como exemplo.

Mas leve em consideração que existem várias formas de contar/estimar as linhas de uma tabela. Veja esta resposta stackoverflow para algumas maneiras diferentes.

Usando sua cláusula LIKE como exemplo, e ignorando "tabelas do sistema" você pode construir este tipo de consulta:

SELECT
  nspname AS schemaname, relname as tablename, reltuples
FROM
  pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' AND
  relname LIKE 'hello%'
ORDER BY reltuples DESC;