Pessoal,
Estou com um problema de um índice que não está funcionando adequadamente.
Gostaria que vocês avaliassem na tentativa de me ajudar a descobrir o que
estou fazendo de errado.
*Explicando o esquema:*
Temos uma tabela *parte* que possui uma FK *id_lote*, o argumento da nossa
consulta.
E uma tabela *pesquisa* com FK *id_parte*.
Particionamos a tabela *Pesquisa* por mês, ou seja, temos uma tabela *
pesquisa* como pai e temos as tabelas de cada mês: *pesquisa_1_2011,
pesquisa_2_2011, pesquisa_3_2011, pesquisa_4_2011*
*Indices: *
Na tabela *Parte*:
CREATE UNIQUE INDEX parte_pkey ON parte USING btree (id);
CREATE INDEX idx_parte_lote ON parte USING btree(id_lote);
Na tabela *Pesquisa*:
CREATE INDEX idx_desc_id_parte_pesquisa ON pesquisa USING
btree(id_parte DESC);
Nas tabelas com INHERIT:
CREATE INDEX idx_desc_id_parte_pesquisa_1_2011 ON pesquisa_1_2011
USING btree(id_parte DESC);
CREATE INDEX idx_desc_id_parte_pesquisa_2_2011 ON pesquisa_2_2011
USING btree(id_parte DESC);
CREATE INDEX idx_desc_id_parte_pesquisa_3_2011 ON pesquisa_3_2011
USING btree(id_parte DESC);
CREATE INDEX idx_desc_id_parte_pesquisa_4_2011 ON pesquisa_4_2011
USING btree(id_parte DESC);
*
*
*Agora queremos retornar a quantidade de pesquisas de um determinado Lote.
Segue os exemplos com **explain**.*
OBS: As consultas que não utilizaram o indice demoraram + de 3 min para
retornar, as que utilizaram o indice demoraram no máximo 5 segundos.
*-- Usando Join (Não Usou o Indice)*
*explain select count(p.id) from pesquisa p,parte a where a.id_lote=290 and
p.id_parte=a.id;*
Aggregate (cost=4452411.77..4452411.78 rows=1 width=8)
-> Hash Join (cost=1259.48..4440371.12 rows=4816259 width=8)
Hash Cond: (p.id_parte = a.id)
-> Append (cost=0.00..4115077.76 rows=73565676 width=16)
-> Seq Scan on pesquisa p (cost=0.00..18.90 rows=890
width=16)
-> Seq Scan on pesquisa_1_2011 p (cost=0.00..1824410.08
rows=32859408 width=16)
-> Seq Scan on pesquisa_2_2011 p (cost=0.00..634935.14
rows=23731014 width=16)
-> Seq Scan on pesquisa_3_2011 p (cost=0.00..631438.43
rows=13768743 width=16)
-> Seq Scan on pesquisa_4_2011 p (cost=0.00..1024275.21
rows=3205621 width=16)
-> Hash (cost=1252.20..1252.20 rows=583 width=8)
-> Index Scan using idx_parte_lote on parte a
(cost=0.00..1252.20 rows=583 width=8)
Index Cond: (id_lote = 290)
*-- Usando Exists (Não Usou o Indice)*
*explain select count(id) from pesquisa p where exists (select null from
parte a where id_lote=290 and p.id_parte=a.id);*
Aggregate (cost=4375068.67..4375068.68 rows=1 width=8)
-> Hash Semi Join (cost=1259.48..4363028.02 rows=4816259 width=8)
Hash Cond: (p.id_parte = a.id)
-> Append (cost=0.00..4115077.76 rows=73565676 width=16)
-> Seq Scan on pesquisa p (cost=0.00..18.90 rows=890
width=16)
-> Seq Scan on pesquisa_1_2011 p (cost=0.00..1824410.08
rows=32859408 width=16)
-> Seq Scan on pesquisa_2_2011 p (cost=0.00..634935.14
rows=23731014 width=16)
-> Seq Scan on pesquisa_3_2011 p (cost=0.00..631438.43
rows=13768743 width=16)
-> Seq Scan on pesquisa_4_2011 p (cost=0.00..1024275.21
rows=3205621 width=16)
-> Hash (cost=1252.20..1252.20 rows=583 width=8)
-> Index Scan using idx_parte_lote on parte a
(cost=0.00..1252.20 rows=583 width=8)
Index Cond: (id_lote = 290)
*-- Usando IN com Select interno (Não Usou o Indice)*
*explain select count(id) from pesquisa where id_parte in
(select id from parte where id_lote=290);*
Aggregate (cost=4375068.67..4375068.68 rows=1 width=8)
-> Hash Semi Join (cost=1259.48..4363028.02 rows=4816259 width=8)
Hash Cond: (experiannet.pesquisa.id_parte = parte.id)
-> Append (cost=0.00..4115077.76 rows=73565676 width=16)
-> Seq Scan on pesquisa (cost=0.00..18.90 rows=890 width=16)
-> Seq Scan on pesquisa_1_2011 pesquisa
(cost=0.00..1824410.08 rows=32859408 width=16)
-> Seq Scan on pesquisa_2_2011 pesquisa
(cost=0.00..634935.14 rows=23731014 width=16)
-> Seq Scan on pesquisa_3_2011 pesquisa
(cost=0.00..631438.43 rows=13768743 width=16)
-> Seq Scan on pesquisa_4_2011 pesquisa
(cost=0.00..1024275.21 rows=3205621 width=16)
-> Hash (cost=1252.20..1252.20 rows=583 width=8)
-> Index Scan using idx_parte_lote on parte
(cost=0.00..1252.20 rows=583 width=8)
Index Cond: (id_lote = 290)
*-- Usando IN com Lista (USOU O INDICE)*
*explain select count(id) from pesquisa where id_parte in
(118236,118241,118242,118244,118246,118257);*
Aggregate (cost=534245.53..534245.54 rows=1 width=8)
-> Append (cost=5.55..533820.35 rows=170072 width=8)
-> Bitmap Heap Scan on pesquisa (cost=5.55..12.67 rows=3 width=8)
Recheck Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_experiannet (cost=0.00..5.55 rows=3 width=0)
Index Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Heap Scan on pesquisa_1_2011 pesquisa
(cost=401.26..73871.67 rows=20150 width=8)
Recheck Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_1_2011_experian (cost=0.00..396.22 rows=20150
width=0)
Index Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Heap Scan on pesquisa_2_2011 pesquisa
(cost=522.06..84225.80 rows=26573 width=8)
Recheck Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_2_2011_experian (cost=0.00..515.41 rows=26573
width=0)
Index Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Heap Scan on pesquisa_3_2011 pesquisa
(cost=534.87..91793.39 rows=28371 width=8)
Recheck Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_3_2011_experian (cost=0.00..527.78 rows=28371
width=0)
Index Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Heap Scan on pesquisa_4_2011 pesquisa
(cost=1885.94..283916.82 rows=94975 width=8)
Recheck Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_4_2011_experiannet (cost=0.00..1862.19
rows=94975 width=0)
Index Cond: (id_parte = ANY
('{118236,118241,118242,118244,118246,118257}'::bigint[]))
*-- Usando ANY com Array (USOU O INDICE)*
*explain select count(id) from pesquisa where id_parte = ANY (array(select
id from parte where id_lote=290)::bigint[]);*
Aggregate (cost=828803.85..828803.86 rows=1 width=8)
InitPlan 1 (returns $0)
-> Index Scan using idx_parte_lote on parte (cost=0.00..1236.05
rows=583 width=8)
Index Cond: (id_lote = 290)
-> Append (cost=6.59..826847.21 rows=288235 width=8)
-> Bitmap Heap Scan on pesquisa (cost=6.59..16.11 rows=6 width=8)
Recheck Cond: (id_parte = ANY ($0))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_experiannet (cost=0.00..6.59 rows=6 width=0)
Index Cond: (id_parte = ANY ($0))
-> Bitmap Heap Scan on pesquisa_1_2011 pesquisa
(cost=675.97..123482.25 rows=34822 width=8)
Recheck Cond: (id_parte = ANY ($0))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_1_2011_experian (cost=0.00..667.26 rows=34822
width=0)
Index Cond: (id_parte = ANY ($0))
-> Bitmap Heap Scan on pesquisa_2_2011 pesquisa
(cost=882.31..132926.49 rows=46093 width=8)
Recheck Cond: (id_parte = ANY ($0))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_2_2011_experian (cost=0.00..870.79 rows=46093
width=0)
Index Cond: (id_parte = ANY ($0))
-> Bitmap Heap Scan on pesquisa_3_2011 pesquisa
(cost=956.09..149525.89 rows=50592 width=8)
Recheck Cond: (id_parte = ANY ($0))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_3_2011_experian (cost=0.00..943.45 rows=50592
width=0)
Index Cond: (id_parte = ANY ($0))
-> Bitmap Heap Scan on pesquisa_4_2011 pesquisa
(cost=3089.47..420896.46 rows=156722 width=8)
Recheck Cond: (id_parte = ANY ($0))
-> Bitmap Index Scan on
idx_desc_id_parte_pesquisa_4_2011_experiannet (cost=0.00..3050.29
rows=156722 width=0)
Index Cond: (id_parte = ANY ($0))
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
