Estudo de Caso I – Parte 5

Siga o passa-a-passo corretamente, não altere os nomes aqui definidos, pois, futuramente serão usados para outros exemplos, ou estudos de caso. Se você alterá-los esteja ciente disto.

Continuando nosso estudo de caso, vamos agora falar sobre índices compostos, dando continuidade ao post anterior.

Índices Compostos

Pegando como exemplo nossa tabela pessoas. Sabemos que nossa tabela pessoas contém a seguinte estrutura.

Table “public.pessoas”
Column | Type | Modifiers
———+————————+——————————————————
id | integer | not null default nextval(‘pessoas_id_seq’::regclass)
nome | varchar(200) | not null
sexo | character(1) | not null
dt_nasc | date | not null
Indexes:
“pk_pessoas_id” PRIMARY KEY, btree (id)


No nosso exemplo, temos somente uma tabela pessoas, com milhares de registros. Então, vamos criar um índice composto para nossa tabela. Mas antes, vamos lembrar como estava nosso EXPLAIN ANALYSE antes de criação do índice.

— Primeira Condição
EXPLAIN ANALYSE SELECT nome, dt_nasc, sexo FROM pessoas WHERE sexo = ‘M’ AND dt_nasc BETWEEN (now() – interval ’28 YEARS’) and now();
QUERY PLAN
——————————————————————————————————————
Seq Scan on pessoas (cost=0.00..32675.00 rows=164615 width=27) (actual time=0.032..568.373 rows=175420 loops=1)
Filter: ((sexo = ‘M’::bpchar) AND (dt_nasc <= now()) AND (dt_nasc >= (now() – ’28 years’::interval)))
Total runtime: 577.328 ms

— Segunda Condição

EXPLAIN ANALYSE SELECT nome, dt_nasc, sexo FROM pessoas WHERE (nome like ‘%VALE%’ AND sexo = ‘M’ AND dt_nasc < (now() – interval ’28 YEARS’) );
QUERY PLAN
———————————————————————————————————————-
Seq Scan on pessoas (cost=0.00..30172.50 rows=33 width=27) (actual time=15.001..235.484 rows=5 loops=1)
Filter: (((nome)::text ~~ ‘%VALE%’::text) AND (sexo = ‘M’::bpchar) AND (dt_nasc < (now() – ’28 years’::interval)))
Total runtime: 235.531 ms

Vamos criar o índice composto, depois de criado nosso objetivo é que seja utilizado na consultas que definimos acima, deixando de usar a busca sequencial para o usar o índice, tornando a busca mais rápida. Vamos criar dois índices com os mesmo campos, mas na ordem diferente, e vemos como os tempos podem ser diferentes.

CREATE INDEX idx_pessoas_idade1 on PESSOAS(sexo, dt_nasc);

CREATE INDEX idx_pessoas_idade2 on PESSOAS(dt_nasc, sexo);

Agora que criamos o índice, vamos chamar nossas consultas, e verificar através do EXPLAIN ANALYSE se nosso índice foi chamado e qual deles é melhor.

Crie um índice e faça o EXPLAIN ANALYSE, depois exclua o índice e crie o outro. Se você criar os dois índices não será possível fazer a analise.

EXPLAIN ANALYSE SELECT nome, dt_nasc, sexo FROM pessoas WHERE sexo = ‘M’ AND dt_nasc BETWEEN (now() – interval ’28 YEARS’) and now();

QUERY PLAN
—————————————————————————————————————————————-
Bitmap Heap Scan on pessoas (cost=2287.87..12353.75 rows=96275 width=27) (actual time=41.913..82.415 rows=102889 loops=1)
Recheck Cond: ((sexo = ‘M’::bpchar) AND (dt_nasc >= (now() – ’28 years’::interval)) AND (dt_nasc <= now()))
-> Bitmap Index Scan on idx_pessoas_idade1 (cost=0.00..2263.80 rows=96275 width=0) (actual time=40.784..40.784 rows=102889 loops=1)
Index Cond: ((sexo = ‘M’::bpchar) AND (dt_nasc >= (now() – ’28 years’::interval)) AND (dt_nasc <= now()))
Total runtime: 85.781 ms

QUERY PLAN
—————————————————————————————————————————————–

Bitmap Heap Scan on pessoas (cost=4596.87..14663.97 rows=96324 width=27) (actual time=48.884..89.403 rows=102889 loops=1)
Recheck Cond: ((dt_nasc >= (now() – ’28 years’::interval)) AND (dt_nasc <= now()) AND (sexo = ‘M’::bpchar))
-> Bitmap Index Scan on idx_pessoas_idade2 (cost=0.00..4572.79 rows=96324 width=0) (actual time=47.721..47.721 rows=102889 loops=1)
Index Cond: ((dt_nasc >= (now() – ’28 years’::interval)) AND (dt_nasc <= now()) AND (sexo = ‘M’::bpchar))
Total runtime: 92.749 ms

Porque a ordem fez a diferença?

A ordem que você define os campos reflete a velocidade de busca no índice. No nosso caso, o índice que começa com dt_nasc, sexo, será criado mais rapidamente, mas é um índice lento em relação o que começa com sexo, dt_nasc, sendo este, mais lento para ser criado, porém muito mais eficaz.


  • sexo – dt_nasc A árvore é eficaz, pois, pelo fato do sexo está em primeiro, o postgres faz a separação entre M ou F e já coloca as datas separadamente, de forma que o filtro faz uma varredura já sabendo qual o sexo.
  • dt_nasc – sexo A árvore é mais lenta, devido ao fato do postgres separar a data primeiro, não sendo eficaz para nosso exemplo.
Segunda Consulta

Nossa segunda consulta também utiliza nosso índice, e faz um outro filtro Filter: ((nome)::text ~~ ‘%VALE%’::text).

EXPLAIN ANALYSE SELECT nome, dt_nasc, sexo FROM pessoas WHERE (nome like ‘%VALE%’ AND sexo = ‘M’ AND dt_nasc < (now() – interval ’28 YEARS’) );

QUERY PLAN
——————————————————————————————————————————————
Bitmap Heap Scan on pessoas (cost=8385.05..25036.55 rows=40 width=27) (actual time=82.230..178.347 rows=13 loops=1)
Recheck Cond: ((sexo = ‘M’::bpchar) AND (dt_nasc < (now() – ’28 years’::interval)))
Filter: ((nome)::text ~~ ‘%VALE%’::text)
-> Bitmap Index Scan on idx_pessoas_idade2 (cost=0.00..8385.03 rows=399667 width=0) (actual time=80.226..80.226 rows=397891 loops=1)
Index Cond: ((sexo = ‘M’::bpchar) AND (dt_nasc < (now() – ’28 years’::interval))) —–Aqui ele utiliza os campos que definimos na índice.
Total runtime: 178.376 ms

No próximo post, vamos entender melhor os identificar possíveis campos para índice, também é possível utilizar índices parciais.