👩🏻‍🌾 😬 🍡 Tornando mais rápida a contagem POSTGRESQL (*) 🚶🏻 🚪 🐩

Costuma-se reclamar que a contagem (*) no PostgreSQL é muito lenta.

Neste artigo, desejo explorar opções para que você obtenha o resultado o mais rápido possível.

Por que a contagem (*) é tão lenta?

A maioria das pessoas entende sem problemas que a seguinte solicitação será executada lentamente:

SELECT count(*)
FROM /*   */;

Afinal, essa é uma consulta complexa, e o PostgreSQL deve calcular o resultado antes de saber quantas linhas conterá.

Mas muitas pessoas ficam chocadas quando descobrem que a seguinte consulta é lenta:

SELECT count(*) FROM large_table;

No entanto, se você pensar novamente, tudo o que foi dito acima é verdadeiro: o PostgreSQL deve calcular o conjunto de resultados antes de poder contar. Como o “contador de linhas mágicas” não está armazenado na tabela (como no MySQL do MyISAM), a única maneira de contar as linhas é olhando para elas.

Portanto, count (*) geralmente executa varreduras de tabela sequenciais, o que pode ser bastante caro.

O "" na contagem () é um problema?

O "*" em SELECT * FROM ... se aplica a todas as colunas. Portanto, muitas pessoas acham que usar count (*) é ineficiente e, em vez disso, use count (id) ou count (1).

Mas o "*" na contagem (*) é completamente diferente, significa simplesmente "string" e não se expande (na verdade, é "um agregado com argumento zero"). A contagem da notação (1) ou a contagem (id) é realmente mais lenta que a contagem (*), porque é necessário verificar se o argumento é NULL ou não (count, como a maioria dos agregados, ignora os argumentos NULL).

Portanto, você não conseguirá nada evitando o "*".

Usando a varredura de índice apenas

É tentador varrer um pequeno índice, não a tabela inteira, para contar o número de linhas. No entanto, isso não é tão simples no PostgreSQL devido à sua estratégia de gerenciamento de simultaneidade em várias versões. Cada versão da linha ("tupla") contém informações sobre para qual instantâneo de banco de dados é visível . Mas essas informações (redundantes) não são armazenadas em índices. Portanto, normalmente não é suficiente contar as entradas no índice, porque o PostgreSQL deve observar a entrada da tabela (“heap tuple”) para garantir que a entrada do índice esteja visível.

Para atenuar esse problema, o PostgreSQL introduziu um mapa de visibilidade , uma estrutura de dados que armazena informações sobre se todas as tuplas em um bloco de tabela são visíveis para todos ou não.
Se a maioria dos blocos da tabela estiver totalmente visível, as varreduras de índice não exigirão visitas frequentes a várias tuplas para determinar a visibilidade. Essa varredura de índice é chamada de "varredura apenas de índice" e geralmente é mais rápido varrer um índice para contar as linhas.

Agora é o VACUUM que suporta o mapa de visibilidade, portanto, verifique se o autovacuum é feito com bastante frequência se você deseja usar um pequeno índice para acelerar a contagem (*).

Usando tabela dinâmica

Eu escrevi acima que o PostgreSQL não armazena o número de linhas em uma tabela.

Manter essa contagem de linhas é uma grande sobrecarga, pois esse evento ocorre com todas as modificações de dados e não compensa. Isso seria um mau negócio. Além disso, como solicitações diferentes podem ver versões diferentes de strings, o contador também deve ser versionado.

Mas nada impede você de implementar esse contador de linha.
Suponha que você queira rastrear o número de linhas em uma tabela de tabelas. Você pode fazer o seguinte:

START TRANSACTION;
 
CREATE TABLE mytable_count(c bigint);
 
CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER mytable_count_mod
   AFTER INSERT OR DELETE ON mytable
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();
 
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;
 
COMMIT;

Fazemos tudo em uma transação para que nenhuma alteração de dados nas transações simultâneas possa ser "perdida" devido a uma condição de toque.
Isso é garantido pelo comando CREATE TRIGGER que bloqueia a tabela no modo SHARE ROW EXCLUSIVE, o que evita todas as alterações simultâneas.
A desvantagem é que todas as modificações de dados paralelas devem esperar até que a contagem SELECT (*) seja executada.

Isso nos fornece uma alternativa muito rápida para contar (*), mas com o custo de diminuir todas as alterações de dados na tabela. O uso de um gatilho de restrição adiada garante que o bloqueio da linha em mytable_count seja o mais curto possível para melhorar a simultaneidade.

Apesar de esta mesa de balcão receber muitas atualizações, não há perigoNão há inchaço na tabela " , porque todas serão atualizações" quentes "(atualizações QUENTES).

Você realmente precisa contar (*)

Às vezes, a melhor solução é procurar uma alternativa.

Geralmente, a aproximação é boa o suficiente e você não precisa da quantidade exata. Nesse caso, você pode usar a pontuação que o PostgreSQL usa para agendar consultas:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

Esse valor é atualizado pelo vácuo automático e pela análise automática, portanto nunca deve exceder 10%. Você pode reduzir autovacuum_analyze_scale_factor para esta tabela para que a análise automática seja executada com mais frequência.

Estimando o número de resultados da consulta

Até agora, temos explorado como acelerar a contagem de linhas da tabela.

Mas, às vezes, você precisa saber quantas linhas a instrução SELECT retornará sem realmente executar a consulta.

Obviamente, a única maneira de obter uma resposta precisa para essa pergunta é concluir a solicitação. Mas se a nota for boa o suficiente, você poderá usar o otimizador PostgreSQL para obtê-la.

A seguinte função simples usa SQL dinâmico e EXPLAIN para obter o plano de execução da consulta como argumento e retorna uma estimativa do número de linhas:

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
 
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

Não use essa função para processar instruções SQL não confiáveis, pois elas são inerentemente vulneráveis à injeção de SQL.

Tornando mais rápida a contagem POSTGRESQL (*)