COUNT(*) que trava a listagem: mostrar o total de registros sem varrer a tabela inteira

O PostgreSQL não guarda o número de linhas de uma tabela em lugar nenhum que possa ser lido com garantia de exatidão. Isso não é descuido, é consequência do MVCC: cada transação enxerga um snapshot próprio, e uma linha inserida por uma transação ainda aberta existe para ela e não existe para as outras. Não há um único total correto; há um total para cada snapshot. Para responder COUNT(*), o banco precisa percorrer as linhas que atendem ao filtro e conferir, uma a uma, se cada versão é visível para a transação atual.

Um índice ajuda menos do que parece. Com um índice em (loja_id, status), o planejador pode usar index-only scan, mas ainda visita cada entrada que casa com o filtro: 12 milhões de pedidos pagos são 12 milhões de entradas lidas. E o index-only scan só evita o heap nas páginas marcadas como all-visible no visibility map. Em uma tabela de pedidos, onde status muda o dia inteiro, uma parte das páginas está sempre suja, e o banco volta ao heap para conferir visibilidade. Sem índice útil, vira parallel seq scan, que termina mais rápido em relógio e consome CPU de vários workers ao mesmo tempo.

Antes de mudar a tela, meça. A primeira consulta abaixo mostra quanto do tempo total do banco vai para contagens, a segunda mostra o plano da contagem mais comum com o volume de buffers lidos e a terceira mostra quanto da tabela está all-visible, o que define se o index-only scan está trabalhando de verdade.

-- Quanto do tempo total do banco vai para contagens.
WITH geral AS (SELECT sum(total_exec_time) AS tempo FROM pg_stat_statements)
SELECT left(regexp_replace(s.query, '\s+', ' ', 'g'), 80) AS consulta,
       s.calls AS chamadas,
       round(s.mean_exec_time::numeric, 1) AS media_ms,
       round((100 * s.total_exec_time / geral.tempo)::numeric, 1) AS pct_do_tempo
  FROM pg_stat_statements s, geral
 WHERE s.query ILIKE '%count(%'
 ORDER BY s.total_exec_time DESC
 LIMIT 10;

-- O plano da contagem que a tela dispara a cada troca de filtro.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
  FROM pedidos
 WHERE loja_id = 42
   AND status = 'pago'
   AND criado_em >= now() - interval '90 days';

-- Quanto da tabela esta marcado como all-visible no visibility map.
-- Abaixo de 90%, o index-only scan volta a ler o heap para conferir visibilidade.
SELECT s.relname,
       s.n_live_tup,
       s.n_dead_tup,
       s.last_autovacuum,
       round(100.0 * c.relallvisible / nullif(c.relpages, 0), 1) AS pct_all_visible
  FROM pg_stat_user_tables s
  JOIN pg_class c ON c.oid = s.relid
 WHERE s.relname = 'pedidos';

O passo que mais economiza não é técnico. É perguntar para que serve o número. Em entrevistas com lojistas do exemplo, ninguém usava o total exato acima de alguns milhares: o número servia para saber se o filtro estava amplo demais, se a fila de pedidos a separar estava crescendo e se valia a pena paginar ou refinar a busca. O total exato só importava em dois lugares: a contagem de pedidos por status no painel da loja, que alimenta metas, e a exportação, que já roda de forma assíncrona.

Com essa resposta, cada lugar da interface ganha o tipo de total adequado, e o contrato da API passa a dizer qual tipo está sendo devolvido. A tabela resume as opções e o custo de cada uma.

A contagem com teto é a técnica mais simples e cobre a maioria dos filtros livres. Em vez de contar todas as linhas, a subconsulta pede no máximo 10.001 e o COUNT conta só o que veio. O executor para de ler assim que atinge o LIMIT, então o custo fica limitado pelo teto, não pelo tamanho do resultado. Se voltarem 10.001, a tela mostra "mais de 10.000", que é exatamente a informação que o lojista usa para decidir refinar o filtro.

Para a navegação, a pergunta "existe próxima página?" não precisa de total nenhum. Peça 51 linhas quando a página mostra 50: se vierem 51, há mais. Combinada com paginação por cursor em (criado_em, id), a listagem fica com custo constante em qualquer página, e o índice que atende à listagem é o mesmo que atende à contagem com teto.

-- Indice que atende a listagem e a contagem com teto pelos mesmos filtros.
CREATE INDEX CONCURRENTLY pedidos_loja_status_criado
    ON pedidos (loja_id, status, criado_em DESC, id DESC);

-- Contagem com teto: o executor para de ler na linha 10.001.
SELECT count(*) AS total
  FROM (
    SELECT 1
      FROM pedidos
     WHERE loja_id = $1
       AND status = $2
       AND criado_em >= $3
     LIMIT 10001
  ) AS amostra;
-- total <= 10000: numero exato. total = 10001: a tela mostra "mais de 10.000".

-- Navegacao sem total: pede uma linha a mais para saber se existe proxima pagina.
SELECT id, cliente_nome, valor_total, criado_em
  FROM pedidos
 WHERE loja_id = $1
   AND status = $2
   AND (criado_em, id) < ($3, $4)
 ORDER BY criado_em DESC, id DESC
 LIMIT 51;
-- 51 linhas voltaram: mostra 50 e habilita "proxima". Menos que isso: e a ultima pagina.

• O teto precisa ser um número que faça sentido para o usuário. 10.000 funcionou no exemplo porque nenhuma operação real olhava mais do que isso.
• A contagem com teto só é barata se o filtro tiver índice. Sem índice, o banco pode varrer milhões de linhas até encontrar as 10.001 que casam, quando o filtro é muito seletivo.
• Proteja a consulta com statement_timeout. Um filtro raro sobre uma coluna sem índice transforma o LIMIT em busca longa, e a tela deve cair para a estimativa em vez de esperar.

Para resultados grandes, o próprio planejador já sabe a resposta aproximada. Sem filtro, pg_class.reltuples guarda o número de linhas calculado pelo último ANALYZE ou VACUUM. Com filtro, um EXPLAIN devolve em "Plan Rows" quantas linhas o planejador espera, sem executar nada. A função abaixo monta a consulta com os filtros da tela, roda o EXPLAIN em JSON e devolve a estimativa em cerca de 1 ms. Os valores entram por format com %L e %s sobre parâmetros tipados, então não há concatenação de texto vindo do usuário.

-- Total da tabela inteira, sem filtro: o numero que o ANALYZE ja calculou.
-- reltuples = -1 significa que a tabela nunca foi analisada (Postgres 14+).
SELECT reltuples::bigint AS total_estimado
  FROM pg_class
 WHERE oid = 'pedidos'::regclass;

-- Total estimado para os filtros da tela, lido do plano sem executar a consulta.
CREATE OR REPLACE FUNCTION pedidos_contagem_estimada(
  p_loja   bigint,
  p_status text,
  p_desde  timestamptz
) RETURNS bigint
LANGUAGE plpgsql
AS $
DECLARE
  filtros text[] := ARRAY['true'];
  plano   json;
BEGIN
  IF p_loja IS NOT NULL THEN
    filtros := filtros || format('loja_id = %s', p_loja);
  END IF;
  IF p_status IS NOT NULL THEN
    filtros := filtros || format('status = %L', p_status);
  END IF;
  IF p_desde IS NOT NULL THEN
    filtros := filtros || format('criado_em >= %L', p_desde);
  END IF;

  EXECUTE format(
    'EXPLAIN (FORMAT JSON) SELECT 1 FROM pedidos WHERE %s',
    array_to_string(filtros, ' AND ')
  ) INTO plano;

  RETURN (plano -> 0 -> 'Plan' ->> 'Plan Rows')::bigint;
END;
$;

-- Loja e status sao correlacionados: sem isso o planejador multiplica as seletividades
-- como se fossem independentes e erra a estimativa por uma ordem de grandeza.
CREATE STATISTICS pedidos_loja_status (dependencies, mcv) ON loja_id, status FROM pedidos;
ALTER TABLE pedidos ALTER COLUMN loja_id SET STATISTICS 1000;
ANALYZE pedidos;

A qualidade da estimativa depende das estatísticas, e esse é o ponto que costuma ser ignorado. Por padrão, o planejador trata loja_id e status como independentes e multiplica as seletividades. Mas lojas grandes concentram pedidos pagos de um jeito que a multiplicação não captura, e no exemplo o erro chegava a 9 vezes para as maiores lojas. CREATE STATISTICS com dependencies e mcv ensina ao planejador a correlação entre as colunas, e aumentar o alvo de estatísticas de loja_id garante que as lojas mais frequentes tenham valor próprio na lista de mais comuns.

Depois do ajuste, compare estimado e real para uma amostra de filtros antes de confiar no número. No exemplo, o erro mediano ficou em 4% e o pior caso em 30%, o que é aceitável para mostrar "cerca de 12 milhões" e inaceitável para qualquer cálculo financeiro. Se o autovacuum não acompanha a tabela, o ANALYZE atrasa e a estimativa envelhece junto; vale baixar autovacuum_analyze_scale_factor para tabelas grandes como esta.

Quando a tela precisa de total exato e os filtros são conhecidos, como pedidos por loja e status no painel, a solução é manter o contador em vez de calcular. A versão ingênua, um UPDATE contador SET total = total + 1 em um trigger, cria um problema pior do que o original: todas as transações que inserem pedido da mesma loja passam a disputar a mesma linha, e as escritas ficam serializadas atrás do lock dessa linha.

A alternativa é registrar deltas. Cada escrita em pedidos só insere uma linha em pedidos_contagem_delta, e inserts não disputam lock entre si. Um job consolida os deltas a cada poucos segundos, somando por loja e status e apagando o que somou na mesma instrução. A leitura soma o consolidado com os deltas pendentes em uma única consulta, que roda sobre o mesmo snapshot e por isso devolve o número exato, mesmo no meio de uma consolidação.

-- Total consolidado por loja e status.
CREATE TABLE pedidos_contagem (
  loja_id bigint NOT NULL,
  status  text   NOT NULL,
  total   bigint NOT NULL,
  PRIMARY KEY (loja_id, status)
);

-- Cada escrita em pedidos so insere uma linha aqui; ninguem disputa a mesma linha.
CREATE TABLE pedidos_contagem_delta (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  loja_id bigint NOT NULL,
  status  text   NOT NULL,
  delta   int    NOT NULL
);
CREATE INDEX pedidos_contagem_delta_filtro ON pedidos_contagem_delta (loja_id, status);

CREATE OR REPLACE FUNCTION pedidos_registrar_delta() RETURNS trigger
LANGUAGE plpgsql
AS $
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    INSERT INTO pedidos_contagem_delta (loja_id, status, delta)
    VALUES (OLD.loja_id, OLD.status, -1);
  END IF;
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    INSERT INTO pedidos_contagem_delta (loja_id, status, delta)
    VALUES (NEW.loja_id, NEW.status, 1);
  END IF;
  RETURN NULL;
END;
$;

-- Carga inicial e trigger na mesma transacao: o lock bloqueia escritas em pedidos
-- enquanto o GROUP BY roda, entao nenhum pedido entra duas vezes nem fica de fora.
BEGIN;
LOCK TABLE pedidos IN SHARE ROW EXCLUSIVE MODE;
CREATE TRIGGER pedidos_contagem_trg
  AFTER INSERT OR DELETE OR UPDATE OF loja_id, status ON pedidos
  FOR EACH ROW EXECUTE FUNCTION pedidos_registrar_delta();
INSERT INTO pedidos_contagem (loja_id, status, total)
SELECT loja_id, status, count(*) FROM pedidos GROUP BY loja_id, status;
COMMIT;

-- Consolidacao, rodada a cada poucos segundos por um job: soma os deltas e apaga o que somou.
WITH consumidos AS (
  DELETE FROM pedidos_contagem_delta
   WHERE id IN (
     SELECT id FROM pedidos_contagem_delta
      ORDER BY id
      LIMIT 50000
        FOR UPDATE SKIP LOCKED
   )
  RETURNING loja_id, status, delta
), somados AS (
  SELECT loja_id, status, sum(delta) AS delta
    FROM consumidos
   GROUP BY loja_id, status
)
INSERT INTO pedidos_contagem AS c (loja_id, status, total)
SELECT loja_id, status, delta FROM somados
ON CONFLICT (loja_id, status) DO UPDATE SET total = c.total + EXCLUDED.total;

-- Leitura exata: consolidado mais o que ainda nao foi consolidado, no mesmo snapshot.
SELECT coalesce(sum(total), 0)::bigint AS total
  FROM (
    SELECT total FROM pedidos_contagem
     WHERE loja_id = $1 AND ($2::text IS NULL OR status = $2)
    UNION ALL
    SELECT delta FROM pedidos_contagem_delta
     WHERE loja_id = $1 AND ($2::text IS NULL OR status = $2)
  ) AS partes;

• A carga inicial e a criação do trigger rodam na mesma transação, com lock que bloqueia escritas em pedidos. No exemplo, o GROUP BY levou 22 segundos e foi feito às 4h; sem o lock, pedidos criados durante a carga entrariam duas vezes ou nenhuma.
• O trigger com UPDATE OF loja_id, status dispara mesmo quando o valor não muda, mas grava -1 e +1 na mesma chave, e a soma continua correta.
• O FOR UPDATE SKIP LOCKED permite rodar a consolidação em mais de uma instância do job sem que duas somem o mesmo delta.
• O contador só cobre os filtros que ele agrega. Filtro por período ou por texto continua indo para teto ou estimativa, e é melhor deixar isso explícito no código do que tentar criar contadores para toda combinação.

As técnicas só funcionam juntas se a escolha ficar em um lugar só. O módulo abaixo recebe os filtros da listagem e decide: filtros cobertos pelo contador vão para ele e voltam exatos; os demais passam primeiro pela estimativa, que custa 1 ms; resultados grandes param ali; resultados menores vão para a contagem com teto, protegida por statement_timeout de 300 ms dentro de uma transação somente leitura. O retorno sempre traz o tipo, e a interface formata de acordo: número inteiro, "mais de 10.000" ou "cerca de 12 milhões".

Pedido de total para a listagem
        |
        v
Filtro = loja (+ status), sem período? --sim--> contador por delta -> exato
        | não
        v
Estimativa do planejador (EXPLAIN, ~1 ms)
        |
        +-- acima de 20.000 ------------------> "cerca de 12 milhões"
        |
        v
Contagem com teto (LIMIT 10.001, timeout 300 ms)
        |
        +-- até 10.000 -----------------------> exato
        +-- 10.001 ---------------------------> "mais de 10.000"
        +-- estourou o tempo -----------------> volta para a estimativa

// pedidos/total.js - decide como calcular o total de uma listagem de pedidos.
import { pool } from '../db.js';

const TETO = 10_000;
const CACHE_MS = 60_000;
const CACHE_MAX = 5_000;
const cache = new Map();

function montarFiltro({ lojaId, status, desde }) {
  const condicoes = [];
  const params = [];
  if (lojaId != null) {
    params.push(lojaId);
    condicoes.push(`loja_id = ${params.length}`);
  }
  if (status != null) {
    params.push(status);
    condicoes.push(`status = ${params.length}`);
  }
  if (desde != null) {
    params.push(desde);
    condicoes.push(`criado_em >= ${params.length}`);
  }
  return { where: condicoes.length ? condicoes.join(' AND ') : 'true', params };
}

// 12.384.917 vira 12.000.000: a tela nao promete precisao que a estimativa nao tem.
function arredondar(valor) {
  if (valor < 1_000) return valor;
  const passo = 10 ** (String(valor).length - 2);
  return Math.round(valor / passo) * passo;
}

async function contarPeloContador({ lojaId, status }) {
  const { rows } = await pool.query(
    `SELECT coalesce(sum(total), 0)::bigint AS total
       FROM (
         SELECT total FROM pedidos_contagem
          WHERE loja_id = $1 AND ($2::text IS NULL OR status = $2)
         UNION ALL
         SELECT delta FROM pedidos_contagem_delta
          WHERE loja_id = $1 AND ($2::text IS NULL OR status = $2)
       ) AS partes`,
    [lojaId, status ?? null],
  );
  return Number(rows[0].total);
}

async function estimar({ lojaId, status, desde }) {
  const { rows } = await pool.query(
    'SELECT pedidos_contagem_estimada($1::bigint, $2::text, $3::timestamptz) AS total',
    [lojaId ?? null, status ?? null, desde ?? null],
  );
  return Number(rows[0].total);
}

// Contagem com teto e tempo maximo; devolve null se estourar o tempo.
async function contarComTeto(filtros) {
  const { where, params } = montarFiltro(filtros);
  const client = await pool.connect();
  try {
    await client.query('BEGIN READ ONLY');
    await client.query("SET LOCAL statement_timeout = '300ms'");
    const { rows } = await client.query(
      `SELECT count(*)::int AS total
         FROM (SELECT 1 FROM pedidos WHERE ${where} LIMIT ${TETO + 1}) AS amostra`,
      params,
    );
    await client.query('COMMIT');
    return rows[0].total;
  } catch (erro) {
    await client.query('ROLLBACK').catch(() => {});
    if (erro.code === '57014') return null; // query_canceled: cai para a estimativa
    throw erro;
  } finally {
    client.release();
  }
}

async function calcular(filtros) {
  const estimado = await estimar(filtros);
  if (estimado > TETO * 2) return { tipo: 'estimado', valor: arredondar(estimado) };

  const contado = await contarComTeto(filtros);
  if (contado === null) return { tipo: 'estimado', valor: arredondar(estimado) };
  if (contado > TETO) return { tipo: 'minimo', valor: TETO };
  return { tipo: 'exato', valor: contado };
}

export async function totalDePedidos(filtros) {
  // Filtros cobertos pelo contador: resposta exata, barata e sem cache.
  if (filtros.lojaId != null && filtros.desde == null) {
    return { tipo: 'exato', valor: await contarPeloContador(filtros) };
  }

  const chave = JSON.stringify([filtros.lojaId ?? null, filtros.status ?? null, filtros.desde ?? null]);
  const guardado = cache.get(chave);
  if (guardado && guardado.expiraEm > Date.now()) return guardado.total;

  const total = await calcular(filtros);
  if (cache.size >= CACHE_MAX) cache.clear();
  cache.set(chave, { total, expiraEm: Date.now() + CACHE_MS });
  return total;
}

Duas decisões de interface completam a mudança. O total passou a vir de um endpoint separado, carregado depois da listagem, então os 50 pedidos aparecem em 9 ms mesmo quando o total demora. E o cache de 60 segundos por combinação de filtros evita recontar quando o lojista volta para a mesma busca, sem afetar os totais exatos, que vêm do contador e não passam pelo cache.