Ativar custos com reconhecimento de cache para planos de consulta de verificação de índice

Para melhorar o desempenho das consultas e reduzir os custos de E/S do banco de dados, é possível ativar o custo de verificação de índice com reconhecimento de cache no AlloyDB para PostgreSQL. Quando você ativa o custo de verificação de índice com reconhecimento de cache, o planejador de consultas do AlloyDB ajusta o custo de E/S estimado das verificações de índice com base em quantas páginas de índice e tabela estão disponíveis no buffer compartilhado quando a execução começa e seleciona o plano de consulta final com base nesses custos ajustados.

Depois que você ativa o reconhecimento de cache, o recurso funciona automaticamente e se ajusta quando as taxas de ocorrência do buffer compartilhado mudam. O reconhecimento de cache também pode funcionar com outras práticas de ajuste de consulta, como definir a estimativa de custo do planejador de consultas do AlloyDB em random_page_cost.

Ativar o reconhecimento de cache

Para ativar o reconhecimento de cache na instância do AlloyDB, defina a flag alloydb.enable_cache_aware_costing (pré-lançamento) como on. Também é possível definir a flag no nível da sessão para afetar os planos de consulta que ocorrem na mesma sessão. Para saber como definir a flag, consulte Configurar as flags de banco de dados de uma instância.

Exemplo de cenário

O exemplo de código a seguir mostra um plano de verificação de índice executado com um cache de buffer compartilhado totalmente aquecido.

explain (analyze, verbose, buffers)
SELECT count(d) FROM t1 WHERE a = 10 AND b > 100 AND c > 100;

------------------ Aggregate  (cost=3908.93..3908.94 rows=1 width=8) (actual time=4.128..4.130 rows=1 loops=1)
   Output: count(d)
   Buffers: shared hit=926
   ->  Index Scan using idx1 on public.t1  (cost=0.43..3906.49 rows=975 width=2) (actual time=0.143..3.205 rows=919 loops=1)
         Output: a, b, c, d
         Index Cond: ((t1.a = 10) AND (t1.b > 100) AND (t1.c > 100))
         Buffers: shared hit=926
   Execution Time: 4.353 ms

Durante essa execução, não houve leituras de E/S. Sem o reconhecimento de cache, o planejador de consultas inclui o custo de E/S para o plano de consulta de verificação de índice. Isso pode fazer com que o plano de consulta de verificação de índice perca para um plano de consulta de verificação sequencial.

O snippet de código a seguir mostra o custo ajustado do plano de consulta quando o reconhecimento de cache está ativado.

explain (verbose)
SELECT count(d) FROM t1 WHERE a = 10 AND b > 100 AND c > 100;

------------------ Aggregate  (cost=29.93..29.94 rows=1 width=8)
   Output: count(d)
   ->  Index Scan using idx1 on public.t1  (cost=0.43..27.49 rows=975 width=2)
         Output: a, b, c, d
         Index Cond: ((t1.a = 10) AND (t1.b > 100) AND (t1.c > 100))

O novo custo, 27.49, para o mesmo plano de consulta de verificação de índice é muito menor do que o custo antigo, 3906.49.