Meu palpite é que às vezes muitas das páginas do heap são armazenadas em buffer, e as consultas são executadas rapidamente. Outras vezes, o buffer é carregado com outras páginas, e então você acaba esperando para ler os dados do disco.

Há dois problemas com esse tipo de consulta:

1. Mesmo que a consulta seja coberta pelo índice, ela tem que buscar tuplas de heap por causa do MVCC - ela não sabe quais dos registros de índice são visíveis para o tx atual. Isso pode ser melhorado se muitas páginas acabarem no Visibility Map . Mas se você atualizar constantemente registros em páginas antigas, isso não vai durar muito.
2. Independentemente das otimizações, isso é apenas uma consulta ruim. A cardinalidade da sua coluna é muito baixa. Se a consulta precisar inspecionar quase todos os dados, ela não será rápida em tabelas grandes. Talvez você deva manter o controle das contagens em uma tabela separada e atualizar essas contagens quando as linhas na tabela principal forem modificadas.

O Postgres mantém estatísticas para as colunas, incluindo os histogramas de quantas linhas de cada valor esperar. Se estatísticas aproximadas forem suficientes para você, você pode simplesmente consultar pg_stats. Outra abordagem pode ser usar amostragem. Também é uma aproximação. Mas se você precisa de números exatos, então você deve considerar fazer algo mais sofisticado e manter as estatísticas em uma tabela separada você mesmo.

Retrabalhar a arquitetura para armazenar estatísticas pré-calculadas

Há um limite para o quanto essa arquitetura pode escalar - e não é muito. Toda vez que você executar essa consulta, você terá que fazer uma varredura sequencial da tabela (os índices são inúteis).

Se esta for uma consulta crítica, você pode reformular sua arquitetura para manter as estatísticas resumidas em uma tabela separada:

1. Adicionar coluna counted default false,
2. Em um trabalho em segundo plano, uma vez a cada N minutos, o salvamento é contabilizado em alguma outra tabela:

with counted as(
  update record_status set counted=true 
  where not counted 
  returning 1
)
update stats set counts=counts+(select count(*) from counted)

Então, quando você precisar fazer a contagem, o que resta é somar as estatísticas e as novas contagens que não foram atualizadas pelo trabalho em segundo plano:

select (select counts from stats)
       +
       (select count(*) from record_status where not counted)

O countedprecisa ser indexado (um índice parcial seria perfeito). Isso dará resultados instantaneamente.

Se você precisar dar suporte à exclusão também, em vez de countedapenas 2 valores, você pode transformá-lo em 3 valores: JUST_INSERTED, JUST_DELETED, COUNTED. Então, em vez de excluir o registro imediatamente - marque-o como JUST_DELETED, para atualizar as estatísticas no mesmo trabalho em segundo plano. Mas, dessa vez, subtraia as contagens.

Com base nos meus testes descritos na minha pergunta revisada e nos conselhos das respostas a perguntas semelhantes (veja aqui e aqui ), implementei as seguintes alterações:

• Foi criado um índice parcial na coluna de status.CREATE INDEX status_not_processed_idx ON metadata (status) WHERE status<>'PROCESSED';
• Mesa aspirada.VACUUM ANALYZE metadata;
• Configurações de vácuo automático ajustadas na mesa.ALTER TABLE metadata SET (autovacuum_vacuum_scale_factor = 0, autovacuum_analyze_scale_factor = 0, autovacuum_vacuum_threshold = 10000, autovacuum_analyze_threshold = 10000);
• Criou uma função fast_row_count(table_name) com base no método descrito aqui :

CREATE OR REPLACE FUNCTION fast_count_rows(table_name text)
RETURNS bigint
AS $$
SELECT
(
  CASE WHEN c.reltuples < 0 THEN NULL
       WHEN c.relpages = 0 THEN float8 '0'
       ELSE c.reltuples / c.relpages END * (pg_catalog.pg_relation_size(c.oid) / pg_catalog.current_setting('block_size')::int)
)::bigint
  FROM pg_catalog.pg_class c
  WHERE c.oid = CONCAT('public.',table_name)::regclass
$$ LANGUAGE SQL STABLE;

corpus=> SELECT fast_count_rows('metadata');
 fast_count_rows
-----------------
         2192335

• Evite usar COUNT(*)ou conte com PROCESSEDvalor:

CREATE OR REPLACE VIEW metadata_queue_statistics AS
SELECT
  COUNT(*) FILTER (WHERE status = 'QUEUED'::text) AS queued,
  fast_count_rows('metadata') - COUNT(*) FILTER (WHERE status = ANY (ARRAY ['QUEUED'::text, 'ERROR'::text])) AS completed,
  COUNT(*) FILTER (WHERE status = 'ERROR'::text) AS failed,
  fast_count_rows('metadata') AS total
FROM metadata
;

corpus=> SELECT * FROM metadata_queue_statistics;
queued | failed | completed |  total
--------+--------+-----------+---------
  19258 |  11372 |   2398829 | 2429459
(1 row)

O teste de desempenho inicial parecia promissor (422 ms), mas testes subsequentes dessa abordagem produziram o mesmo problema de desempenho:

corpus=> EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM metadata_queue_statistics;

                        QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=127854.78..127855.29 rows=1 width=32) (actual time=76853.872..76853.872 rows=1 loops=1)
   Output: count(*) FILTER (WHERE (metadata.status = 'QUEUED'::text)), (fast_count_rows('metadata'::text) - count(*) FILTER (WHERE (metadata.status = ANY ('{QUEUED,ERROR}'::text[])))), count(*) FILTER (WHERE (metadata.status = 'ERROR'::text)), fast_count_rows('metadata'::text)
   Buffers: shared hit=295240 read=26638 dirtied=383 written=18
   ->  Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..91412.89 rows=2429459 width=9) (actual time=0.071..76432.532 rows=2429459 loops=1)
         Output: metadata.status
         Heap Fetches: 28878
         Buffers: shared hit=295234 read=26638 dirtied=383 written=18
 Planning Time: 124.091 ms
 Execution Time: 76853.911 ms
(9 rows)

Não vou marcar isso como resposta para minha pergunta porque o problema de desempenho persistiu. Estou deixando isso aqui como um exemplo de uma solução que falhou.

Também tentou:

• Aumentou shared_buffersde postgresql.confpadrão 128MBpara 2GB. (nenhuma alteração significativa no desempenho da consulta)

Esse problema finalmente chegou a um ponto em que não pude mais ignorá-lo.

Finalmente resolvi esse problema de desempenho movendo o código de status para sua própria tabela e usando um inteiro luid(identificador exclusivo local de incremento automático) da tabela principal como chave. O código de status em si agora é apenas um único caractere. O banco de dados tem até 4.736.786 registros e a visualização (também modificada, colada abaixo) conclui a consulta usando a nova tabela em menos de um segundo.

ALTER TABLE jgi_metadata ADD CONSTRAINT unique_luid UNIQUE (luid);

DROP TABLE record_status; -- earlier attempt

CREATE TABLE record_status
(
  luid integer NOT NULL REFERENCES jgi_metadata(luid),
  status_code char(1) NOT NULL DEFAULT 'Q',
  status text,
  PRIMARY KEY (luid)
);
ALTER TABLE record_status ADD CONSTRAINT status_ck CHECK (status_code = ANY (ARRAY['Q'::char(1), 'P'::char(1), 'E'::char(1)]));

INSERT INTO record_status (luid,status) SELECT luid,status FROM jgi_metadata;

UPDATE record_status SET status_code='Q' WHERE status='QUEUED';
UPDATE record_status SET status_code='P' WHERE status='PROCESSED';
UPDATE record_status SET status_code='E' WHERE status='ERROR';

ALTER TABLE record_status DROP COLUMN status;

CREATE INDEX ON record_status(luid);
CREATE INDEX ON record_status(status_code);

ALTER TABLE jgi_metadata DROP COLUMN status;

Tabela "public.record_status"

   Column    |     Type     | Collation | Nullable |   Default   | Storage  | Stats target | Description
-------------+--------------+-----------+----------+-------------+----------+--------------+-------------
 luid        | integer      |           | not null |             | plain    |              |
 status_code | character(1) |           | not null | 'Q'::bpchar | extended |              |
Indexes:
    "record_status_pkey" PRIMARY KEY, btree (luid)
    "record_status_luid_idx" btree (luid)
    "record_status_status_code_idx" btree (status_code)
Check constraints:
    "status_ck" CHECK (status_code = ANY (ARRAY['Q'::character(1), 'P'::character(1), 'E'::character(1)]))
Foreign-key constraints:
    "record_status_luid_fkey" FOREIGN KEY (luid) REFERENCES metadata(luid)

Também simplifiquei a visualização de estatísticas removendo a fast_count_rows()função (que usava bigint), o que cortou outros 500 ms da consulta:

Exibir "public.metadata_queue_statistics"

CREATE OR REPLACE VIEW metadata_queue_statistics AS
 SELECT count(*) FILTER (WHERE record_status.status_code = 'Q'::char(1)) AS queued,
        count(*) FILTER (WHERE record_status.status_code = 'P'::char(1)) AS completed,
        count(*) FILTER (WHERE record_status.status_code = 'E'::char(1)) AS failed,
        count(*) AS total
  FROM record_status
;

Desempenho antes de remover fast_count_rowsa função da exibição:

corpus=# EXPLAIN ANALYZE SELECT * FROM metadata_queue_statistics;
                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=170413.05..170413.56 rows=1 width=32) (actual time=828.100..828.101 rows=1 loops=1)
   ->  Seq Scan on record_status  (cost=0.00..98880.42 rows=4768842 width=2) (actual time=0.009..337.786 rows=4736786 loops=1)
 Planning Time: 0.227 ms
 Execution Time: 828.123 ms
(4 rows)

Desempenho após remover fast_count_rowsa função da visualização:

corpus=# EXPLAIN ANALYZE SELECT * FROM metadata_queue_statistics;
                                                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=106835.22..106835.23 rows=1 width=32) (actual time=360.787..379.916 rows=1 loops=1)
   ->  Gather  (cost=106834.99..106835.20 rows=2 width=32) (actual time=360.502..379.910 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=105834.99..105835.00 rows=1 width=32) (actual time=349.462..349.462 rows=1 loops=3)
               ->  Parallel Seq Scan on record_status  (cost=0.00..71062.18 rows=1987018 width=2) (actual time=0.181..164.633 rows=1578929 loops=3)
 Planning Time: 0.083 ms
 Execution Time: 379.953 ms
(8 rows)

Então, agora tenho essa visualização concluída em menos de meio segundo.

Para comparação com os tamanhos de buffer das minhas tentativas anteriores de resolver este problema, aqui está o plano de execução mais detalhado:

corpus=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM metadata_queue_statistics;

                 QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=106835.22..106835.23 rows=1 width=32) (actual time=402.090..406.696 rows=1 loops=1)
   Output: count(*) FILTER (WHERE (record_status.status_code = 'Q'::character(1))), count(*) FILTER (WHERE (record_status.status_code = 'P'::character(1))), count(*) FILTER (WHERE (record_status.status_code = 'E'::character(1))), count(*)
   Buffers: shared hit=51192
   ->  Gather  (cost=106834.99..106835.20 rows=2 width=32) (actual time=382.500..406.686 rows=3 loops=1)
         Output: (PARTIAL count(*) FILTER (WHERE (record_status.status_code = 'Q'::character(1)))), (PARTIAL count(*) FILTER (WHERE (record_status.status_code = 'P'::character(1)))), (PARTIAL count(*) FILTER (WHERE (record_status.status_code = 'E'::character(1)))), (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=51192
         ->  Partial Aggregate  (cost=105834.99..105835.00 rows=1 width=32) (actual time=378.003..378.003 rows=1 loops=3)
               Output: PARTIAL count(*) FILTER (WHERE (record_status.status_code = 'Q'::character(1))), PARTIAL count(*) FILTER (WHERE (record_status.status_code = 'P'::character(1))), PARTIAL count(*) FILTER (WHERE (record_status.status_code = 'E'::character(1))), PARTIAL count(*)
               Buffers: shared hit=51192
               Worker 0: actual time=366.127..366.128 rows=1 loops=1
                 Buffers: shared hit=6728
               Worker 1: actual time=385.566..385.566 rows=1 loops=1
                 Buffers: shared hit=7750
               ->  Parallel Seq Scan on public.record_status  (cost=0.00..71062.18 rows=1987018 width=2) (actual time=1.255..188.238 rows=1578929 loops=3)
                     Output: record_status.status_code
                     Buffers: shared hit=51192
                     Worker 0: actual time=3.727..181.228 rows=1111377 loops=1
                       Buffers: shared hit=6728
                     Worker 1: actual time=0.030..211.375 rows=1073500 loops=1
                       Buffers: shared hit=7750
 Planning Time: 0.079 ms
 Execution Time: 406.726 ms
(24 rows)

Não sei como foi, mas preciso saber o seguinte:

• Qual é a sua versão do PostgreSQL e o arquivo de configuração? Seu hardware, o tipo de disco do seu volume de dados/índice ou partição

Existem tantas variáveis ​​que eu resumiria em duas questões:

(1) Como @Stanislav disse acima, a cardinalidade do índice de status é muito baixa para se beneficiar do índice baseado em heap.

(2) A tabela tem muitos campos TEXT e pode aumentar a linha em cada página apenas para fornecer um número exato de linhas e, por causa da coluna TEXT no meio da linha inteira, seu desempenho seria ruim, explicando que seu ANALYZE o torna bom no início e, em seguida, despeja tudo dos shared_buffers.

--> For the second issue, here is what I did, create two tables, one to store its critical result such as media_type, status, thing .. that technically don't have TEXT field in it. And the other has UNIQUE FOREIGN KEY with PRIMARY KEY from the first table.

--> For the INSERT and UPDATE and DELETE, in your app, making an explicit transaction include two INSERT (or UPDATE/DELETE) in it. If both are OK, apply COMMIT and/or ROLLBACK if you don't like it. Then if you want to extract it content, use LEFT JOIN (not INNER JOIN). In here, the main (first) table is much smaller, allowing you to load and get the COUNT on the same amount of rows in less number of pages required to scan.