Por que seu índice composto não está deixando a consulta rápida

8 de setembro de 2026 · postgresql, indexing

Andei lendo o Use The Index, Luke! e queria aplicar o que estava lendo em vez de só concordar com a cabeça, então construí um fintech ledger: um schema de extrato bancário com linhas suficientes para que decisões de índice deixem de ser teóricas. Cada tópico do livro que eu quiser provar vira um estudo de caso nesse repositório, e outros vêm por aí. Este é o primeiro deles: a consulta que todo app de banco roda, uma conta em um período.

O cenário é PostgreSQL 18.4, uma tabela transactions com 10 milhões de linhas distribuídas entre 100 mil contas. Cada plano abaixo foi coletado com apenas o índice em questão existindo na tabela, e com o cache já quente, e é por isso que os Buffers aparecem majoritariamente como hit nos planos indexados.

A consulta

A consulta testada é a get_statement.sql:

select
	*
from
	transactions
where
	account_id = 47
	and created_at between '2025-01-01' and '2025-04-01'
order by
	created_at desc;

Repare que existem duas condições para o where, account_id e created_at, além do order by.

Sem índice nenhum

Se rodarmos esta consulta sem índice nenhum, o banco vai precisar fazer um Seq Scan na tabela inteira pelas duas condições.

Além disso, vai precisar aplicar um algoritmo de sort para ordenar por created_at, assim como mostrado abaixo:

explain (analyze, buffers)
select
	*
from
	transactions
where
	account_id = 47
	and created_at between '2025-01-01' and '2025-04-01'
order by
	created_at desc;

                                                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=188493.05..188498.29 rows=45 width=61) (actual time=200.328..204.925 rows=5.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=2579 read=112075
   ->  Sort  (cost=187493.02..187493.07 rows=19 width=61) (actual time=187.914..187.915 rows=1.67 loops=3)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=2579 read=112075
         Worker 0:  Sort Method: quicksort  Memory: 25kB
         Worker 1:  Sort Method: quicksort  Memory: 25kB
         ->  Parallel Seq Scan on transactions  (cost=0.00..187492.62 rows=19 width=61) (actual time=84.043..187.852 rows=1.67 loops=3)
               Filter: ((created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2025-04-01 00:00:00+00'::timestamp with time zone) AND (account_id = 47))
               Rows Removed by Filter: 3333332
               Buffers: shared hit=2503 read=112075
 Planning:
   Buffers: shared hit=117
 Planning Time: 0.304 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.697 ms (Deform 0.250 ms), Inlining 0.000 ms, Optimization 0.567 ms, Emission 7.885 ms, Total 9.149 ms
 Execution Time: 220.180 ms
(22 rows)

Olhe o Rows Removed by Filter: 3333332. São 3,3 milhões de linhas descartadas por worker, 10 milhões no total, para devolver 5. E os Buffers somam 114.654 blocos (hit=2579 + read=112075), ou seja, cerca de 896 MB lidos para entregar essas 5 linhas.

O índice errado: (created_at, account_id)

Agora, vamos analisar o caso de adicionar índices. Nós poderíamos colocar um índice somente em account_id, o que já otimizaria muito a consulta. Entretanto, como a busca pela conta e a data andam de mãos dadas em extratos, é interessante que criemos um índice com ambos os campos, não acha? Do contrário, o banco precisaria acessar cada transação no heap para saber se a data, não considerada no índice, está entre 01/01/2025 e 01/04/2025 ou não.

Em um primeiro momento, talvez você pense em criar o índice (created_at, account_id), por achar que a ordem das colunas em um índice não importa. Mas importa, e importa muito.

create index idx_transactions_created_at_account_id on transactions (created_at, account_id)

Isso seria um erro. Basta analisar a estrutura de uma B-tree para descobrir por quê. Para fazer a travessia, da raiz até as folhas, o banco se baseia no primeiro campo do índice: created_at. Na nossa consulta, você faria a busca incluindo transações entre 01/01/2025 e 01/04/2025. Três meses de transações no total, de todas as contas, seriam incluídas nisso. Ao chegar nas folhas no range que você definiu para o created_at, você vai perceber que os account_id só estão ordenados para os casos em que o created_at for igual. Ou seja, quase nunca, já que created_at inclui hora, minuto e segundo. Dessa forma, para achar os dados com account_id correto nesses três meses de transação, você vai ter que percorrer tupla a tupla, folha por folha, para achar as transações com account_id = 47.

Assim, a ordem de criação dos campos do índice fez com que account_id deixasse de delimitar a busca e virasse um mero filtro, aplicado linha a linha. E esse índice, goste ou não, continua ocupando espaço no disco do servidor e adiciona tempo em inserções e updates da tabela de transações.

                                                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using idx_transactions_created_at_account_id on transactions  (cost=0.43..25883.90 rows=45 width=61) (actual time=1.974..16.989 rows=5.00 loops=1)
   Index Cond: ((created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2025-04-01 00:00:00+00'::timestamp with time zone) AND (account_id = 47))
   Index Searches: 1
   Buffers: shared hit=3655
 Planning:
   Buffers: shared hit=137
 Planning Time: 0.314 ms
 Execution Time: 17.021 ms
(8 rows)

Repare que aqui não aparece Rows Removed by Filter nenhum, mas o descarte acontece do mesmo jeito. Ele fica implícito, porque o filtro roda dentro do Index Scan e o PostgreSQL não reporta isso.

O motivo é que o Index Cond mostra dois tipos de predicado sob o mesmo rótulo. O access predicate define onde a varredura começa e onde termina. O filter predicate só descarta linhas durante a varredura, sem encurtá-la. Aqui, created_at é access e account_id = 47 é filter, já que uma coluna que vem depois de um range no índice não delimita mais nada. No plano seguinte, as mesmas três condições vão ser todas access.

Como o plano não diferencia os dois, a única forma de saber qual é qual é comparar o Index Cond com a definição do índice.

O índice certo: (account_id, created_at)

Agora, imaginemos o cenário do índice (account_id, created_at):

create index idx_transactions_account_id_created_at on transactions (account_id, created_at)

Para fazer a mesma consulta, você faria a busca na B-tree somente para as transações da conta account_id = 47. Você chegaria nas folhas e teria todas as datas ordenadas, já que você só está buscando as transações de uma conta específica, a 47. Com isso, o banco desce direto até a primeira transação da conta 47 em 01/01/2025 e lê as folhas em sequência, parando assim que a data passa de 01/04/2025. Nenhuma linha lida é descartada.

Se você for criar um índice concatenado, prefira colocar primeiro as colunas que você vai usar com = no where e, só depois, as colunas que você vai usar com range ou between.

                                                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using idx_transactions_account_id_created_at on transactions  (cost=0.43..189.47 rows=46 width=61) (actual time=0.027..0.030 rows=5.00 loops=1)
   Index Cond: ((account_id = 47) AND (created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2025-04-01 00:00:00+00'::timestamp with time zone))
   Index Searches: 1
   Buffers: shared hit=11
 Planning:
   Buffers: shared hit=140
 Planning Time: 0.324 ms
 Execution Time: 0.060 ms
(8 rows)

O que mudou

Repare também no que sumiu do plano. Lá no começo, sem índice nenhum, o banco precisava de um nó Sort para atender o order by created_at desc. Nos dois planos com índice esse nó não aparece mais, e no lugar dele vem Index Scan Backward. Como o índice já guarda os created_at ordenados dentro de cada conta, o banco não precisa ordenar nada. Ele só lê as folhas ao contrário. O order by saiu de graça, junto com o resto.

Agora, perceba a diferença gritante entre as duas execuções, só trocando a ordem das colunas do índice. Na primeira, com (created_at, account_id), o executor teve que ler 3.655 blocos do banco de dados (Buffers: shared hit=3655). Repare que o número que importa é a soma de hit e read, e não só o read. Os dois são blocos que o executor precisou ler, e o hit apenas indica que aquele bloco já estava em cache. Considerando que um bloco é a menor unidade possível de um banco e que possui 8 KB cada, isso equivale a 28,5 MB. Enquanto isso, na segunda execução, com (account_id, created_at), o executor leu apenas 11 blocos, equivalendo a míseros 88 KB.

Isso impactou diretamente o tempo de execução: 17 ms contra 0,060 ms.