Pessoal,
Tava com uma query ruim, peguei seu plano com explain analyze.
Resolvi criar um índice pra ver se melhorava a situação e melhorou.
O engraçado é que pelo mesmo explain analyze esse novo índice não foi
utilizado.
O que pode ser?
Obs: Depois de cada criação/exclusão de índice fiz um analyze na tabela.
explain analyze
SELECT situacaopr0_.id_situacao_processo AS id1_398_,
situacaopr0_.nm_actorid AS nm2_398_, situacaopr0_.dt_create_task AS
dt3_398_, situacaopr0_.id_caixa AS id4_398_, situacaopr0_.id_lote AS
id5_398_, situacaopr0_.id_orgao_julgador_colegiado AS id6_398_,
situacaopr0_.id_orgao_julgador AS id7_398_, situacaopr0_.id_previous_task
AS id8_398_, situacaopr0_.id_process_instance AS id9_398_,
situacaopr0_.id_processo_trf AS id10_398_, situacaopr0_.id_tarefa AS
id11_398_, situacaopr0_.id_task AS id12_398_, situacaopr0_.id_task_instance
AS id13_398_, situacaopr0_.nm_caixa AS nm14_398_, situacaopr0_.nm_fluxo AS
nm15_398_, situacaopr0_.nm_tarefa AS nm16_398_, situacaopr0_.vl_prioridade
AS vl17_398_, situacaopr0_.in_apreciado_segredo AS in18_398_,
situacaopr0_.in_segredo_justica AS in19_398_ FROM vs_situacao_processo_new
situacaopr0_ WHERE situacaopr0_.id_orgao_julgador = '23' AND EXISTS (
SELECT 1 FROM tb_proc_localizacao_ibpm tl WHERE tl.id_processo =
situacaopr0_.id_processo_trf AND tl.id_task_jbpm = situacaopr0_.id_task AND
tl.id_localizacao = '6' AND tl.id_papel = '1470' ) AND (
situacaopr0_.in_segredo_justica = 'N' OR EXISTS ( SELECT
pvs.id_processo_trf FROM tb_proc_visibilida_segredo pvs WHERE pvs.id_pessoa
= '70189' AND pvs.id_processo_trf = situacaopr0_.id_processo_trf ) ) AND
situacaopr0_.id_situacao_processo = '20158536' AND situacaopr0_.id_tarefa =
'61' LIMIT '1';
Plano sem o índice que criei
Limit (cost=0.00..17830.33 rows=1 width=157) (actual
time=3636.961..3636.962 rows=1 loops=1)
-> Nested Loop Semi Join (cost=0.00..17830.33 rows=1 width=157) (actual
time=3636.961..3636.961 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..63.71 rows=1 width=161)
(actual time=0.063..0.063 rows=1 loops=1)
Join Filter: ((c.id_tarefa)::integer =
(tj.id_tarefa)::integer)
-> Nested Loop Left Join (cost=0.00..55.43 rows=1
width=143) (actual time=0.061..0.061 rows=1 loops=1)
-> Nested Loop (cost=0.00..55.14 rows=1 width=139)
(actual time=0.059..0.059 rows=1 loops=1)
-> Nested Loop (cost=0.00..53.12 rows=1
width=131) (actual time=0.052..0.052 rows=1 loops=1)
-> Nested Loop (cost=0.00..44.84 rows=1
width=123) (actual time=0.044..0.044 rows=1 loops=1)
-> Nested Loop (cost=0.00..44.50
rows=1 width=115) (actual time=0.036..0.036 rows=1 loops=1)
-> Nested Loop
(cost=0.00..36.14 rows=1 width=111) (actual time=0.029..0.029 rows=1
loops=1)
-> Nested Loop
(cost=0.00..35.86 rows=1 width=92) (actual time=0.025..0.025 rows=1 loops=1)
-> Index Scan
using idx_taskinst_tokn on jbpm_taskinstance taskinst (cost=0.00..27.49
rows=1 width=76) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond:
(token_ = 20158536::bigint)
Filter: ((NOT
issuspended_) AND isopen_)
-> Index Scan
using jbpm_processinstance_pkey on jbpm_processinstance procinst
(cost=0.00..8.35 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond:
(id_ = taskinst.procinst_)
-> Index Scan using
jbpm_processdefinition_pkey on jbpm_processdefinition procdef
(cost=0.00..0.27 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id_ =
procinst.processdefinition_)
-> Index Scan using
idx_processo_instance_2 on tb_processo_instance procinstcore
(cost=0.00..8.35 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (id_proc_inst
= procinst.id_)
-> Index Scan using sys_c005762 on
tb_processo proc (cost=0.00..0.33 rows=1 width=8) (actual
time=0.008..0.008 rows=1 loops=1)
Index Cond:
((id_processo)::integer = procinstcore.id_processo)
-> Index Scan using idx_tb_tarefa_jbpm1 on
tb_tarefa_jbpm tj (cost=0.00..8.27 rows=1 width=8) (actual
time=0.007..0.007 rows=1 loops=1)
Index Cond: ((id_jbpm_task)::integer
= taskinst.task_)
Filter: ((id_tarefa)::integer = 61)
-> Index Scan using tb_processo_trf_fk on
tb_processo_trf proctrf (cost=0.00..2.01 rows=1 width=16) (actual
time=0.007..0.007 rows=1 loops=1)
Index Cond: ((id_processo_trf)::integer =
(proc.id_processo)::integer)
Filter: (((id_orgao_julgador)::integer =
23) AND (((in_segredo_justica)::bpchar = 'N'::bpchar) OR (alternatives:
SubPlan 5 or hashed SubPlan 6)))
SubPlan 5
-> Seq Scan on
tb_proc_visibilida_segredo pvs (cost=0.00..1.69 rows=1 width=0) (never
executed)
Filter: (((id_pessoa)::integer =
70189) AND ((id_processo_trf)::integer =
(proctrf.id_processo_trf)::integer))
SubPlan 6
-> Seq Scan on
tb_proc_visibilida_segredo pvs (cost=0.00..1.58 rows=1 width=4) (never
executed)
Filter: ((id_pessoa)::integer =
70189)
-> Index Scan using idx_tb_processo_lote1 on
tb_processo_lote proc_lote (cost=0.00..0.27 rows=1 width=8) (actual
time=0.001..0.001 rows=0 loops=1)
Index Cond: ((proctrf.id_processo_trf)::integer =
(id_processo_trf)::integer)
-> Index Scan using tb_caixa_pkey on tb_caixa c
(cost=0.00..8.27 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: ((proc.id_caixa)::integer =
(id_caixa)::integer)
-> Index Scan using tb_processo_localizacao_ibpm_id_processo_idx
on tb_proc_localizacao_ibpm tl (cost=0.00..8.63 rows=1 width=8) (actual
time=0.009..0.009 rows=1 loops=1)
Index Cond: (((id_processo)::integer =
(proctrf.id_processo_trf)::integer) AND ((id_task_jbpm)::integer =
(tj.id_jbpm_task)::integer) AND ((id_papel)::integer = 1470))
Filter: ((id_localizacao)::integer = 6)
SubPlan 3
-> Index Scan using jbpm_taskinstance_pkey on jbpm_taskinstance
ti (cost=17731.35..17740.02 rows=1 width=8) (actual time=0.001..0.001
rows=0 loops=1)
Index Cond: (id_ = $3)
InitPlan 2 (returns $3)
-> Result (cost=17731.34..17731.35 rows=1 width=0)
(actual time=3636.852..3636.853 rows=1 loops=1)
InitPlan 1 (returns $2)
-> Limit (cost=0.00..17731.34 rows=1 width=8)
(actual time=3636.849..3636.849 rows=0 loops=1)
-> Index Scan Backward using
jbpm_taskinstance_pkey on jbpm_taskinstance ti2 (cost=0.00..195044.75
rows=11 width=8) (actual time=3636.848..3636.848 rows=0 loops=1)
Index Cond: (id_ IS NOT NULL)
Filter: ((task_ <> taskinst.task_)
AND (procinst_ = taskinst.procinst_))
SubPlan 4
-> Aggregate (cost=9.34..9.35 rows=1 width=4) (actual
time=0.018..0.018 rows=1 loops=1)
-> Nested Loop (cost=0.00..9.34 rows=1 width=4) (actual
time=0.013..0.013 rows=0 loops=1)
Join Filter: ((pri.id_prioridade_processo)::integer =
(pripro.id_prioridade_processo)::integer)
-> Index Scan using
tb_processo_prioridade_processo_unq on tb_proc_prioridde_processo pripro
(cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((id_processo_trf)::integer =
(proctrf.id_processo_trf)::integer)
-> Seq Scan on tb_prioridade_processo pri
(cost=0.00..1.03 rows=3 width=8) (never executed)
*Total runtime: 3637.232 ms*
Criei o índice
CREATE INDEX idx_taskinst_tsk_ubd
ON jbpm_taskinstance
USING btree
(task_, procinst_,id_);
Limit (cost=0.00..17668.08 rows=1 width=157) (actual
time=2917.138..2917.138 rows=1 loops=1)
-> Nested Loop Semi Join (cost=0.00..17668.08 rows=1 width=157) (actual
time=2917.138..2917.138 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..63.57 rows=1 width=161)
(actual time=0.074..0.074 rows=1 loops=1)
Join Filter: ((c.id_tarefa)::integer =
(tj.id_tarefa)::integer)
-> Nested Loop Left Join (cost=0.00..55.28 rows=1
width=143) (actual time=0.073..0.073 rows=1 loops=1)
-> Nested Loop (cost=0.00..55.00 rows=1 width=139)
(actual time=0.071..0.071 rows=1 loops=1)
-> Nested Loop (cost=0.00..52.98 rows=1
width=131) (actual time=0.063..0.063 rows=1 loops=1)
-> Nested Loop (cost=0.00..52.70 rows=1
width=112) (actual time=0.057..0.057 rows=1 loops=1)
-> Nested Loop (cost=0.00..44.42
rows=1 width=104) (actual time=0.040..0.040 rows=1 loops=1)
-> Nested Loop
(cost=0.00..44.07 rows=1 width=96) (actual time=0.031..0.031 rows=1 loops=1)
-> Nested Loop
(cost=0.00..35.71 rows=1 width=92) (actual time=0.024..0.024 rows=1 loops=1)
-> Index Scan
using idx_taskinst_tokn on jbpm_taskinstance taskinst (cost=0.00..27.35
rows=1 width=76) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond:
(token_ = 20158536::bigint)
Filter: ((NOT
issuspended_) AND isopen_)
-> Index Scan
using jbpm_processinstance_pkey on jbpm_processinstance procinst
(cost=0.00..8.35 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond:
(id_ = taskinst.procinst_)
-> Index Scan using
idx_processo_instance_2 on tb_processo_instance procinstcore
(cost=0.00..8.35 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond:
(id_proc_inst = procinst.id_)
-> Index Scan using
sys_c005762 on tb_processo proc (cost=0.00..0.33 rows=1 width=8) (actual
time=0.008..0.008 rows=1 loops=1)
Index Cond:
((id_processo)::integer = procinstcore.id_processo)
-> Index Scan using
idx_tb_tarefa_jbpm1 on tb_tarefa_jbpm tj (cost=0.00..8.27 rows=1 width=8)
(actual time=0.017..0.017 rows=1 loops=1)
Index Cond:
((id_jbpm_task)::integer = taskinst.task_)
Filter: ((id_tarefa)::integer =
61)
-> Index Scan using
jbpm_processdefinition_pkey on jbpm_processdefinition procdef
(cost=0.00..0.27 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id_ =
procinst.processdefinition_)
-> Index Scan using tb_processo_trf_fk on
tb_processo_trf proctrf (cost=0.00..2.01 rows=1 width=16) (actual
time=0.007..0.007 rows=1 loops=1)
Index Cond: ((id_processo_trf)::integer =
(proc.id_processo)::integer)
Filter: (((id_orgao_julgador)::integer =
23) AND (((in_segredo_justica)::bpchar = 'N'::bpchar) OR (alternatives:
SubPlan 5 or hashed SubPlan 6)))
SubPlan 5
-> Seq Scan on
tb_proc_visibilida_segredo pvs (cost=0.00..1.69 rows=1 width=0) (never
executed)
Filter: (((id_pessoa)::integer =
70189) AND ((id_processo_trf)::integer =
(proctrf.id_processo_trf)::integer))
SubPlan 6
-> Seq Scan on
tb_proc_visibilida_segredo pvs (cost=0.00..1.58 rows=1 width=4) (never
executed)
Filter: ((id_pessoa)::integer =
70189)
-> Index Scan using idx_tb_processo_lote1 on
tb_processo_lote proc_lote (cost=0.00..0.27 rows=1 width=8) (actual
time=0.002..0.002 rows=0 loops=1)
Index Cond: ((proctrf.id_processo_trf)::integer =
(id_processo_trf)::integer)
-> Index Scan using tb_caixa_pkey on tb_caixa c
(cost=0.00..8.27 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((proc.id_caixa)::integer =
(id_caixa)::integer)
-> Index Scan using tb_processo_localizacao_ibpm_id_processo_idx
on tb_proc_localizacao_ibpm tl (cost=0.00..8.63 rows=1 width=8) (actual
time=0.009..0.009 rows=1 loops=1)
Index Cond: (((id_processo)::integer =
(proctrf.id_processo_trf)::integer) AND ((id_task_jbpm)::integer =
(tj.id_jbpm_task)::integer) AND ((id_papel)::integer = 1470))
Filter: ((id_localizacao)::integer = 6)
SubPlan 3
-> Index Scan using jbpm_taskinstance_pkey on jbpm_taskinstance
ti (cost=17569.24..17577.91 rows=1 width=8) (actual time=0.001..0.001
rows=0 loops=1)
Index Cond: (id_ = $3)
InitPlan 2 (returns $3)
-> Result (cost=17569.23..17569.24 rows=1 width=0)
(actual time=2917.029..2917.029 rows=1 loops=1)
InitPlan 1 (returns $2)
-> Limit (cost=0.00..17569.23 rows=1 width=8)
(actual time=2917.026..2917.026 rows=0 loops=1)
-> Index Scan Backward using
jbpm_taskinstance_pkey on jbpm_taskinstance ti2 (cost=0.00..193261.51
rows=11 width=8) (actual time=2917.025..2917.025 rows=0 loops=1)
Index Cond: (id_ IS NOT NULL)
Filter: ((task_ <> taskinst.task_)
AND (procinst_ = taskinst.procinst_))
SubPlan 4
-> Aggregate (cost=9.34..9.35 rows=1 width=4) (actual
time=0.012..0.013 rows=1 loops=1)
-> Nested Loop (cost=0.00..9.34 rows=1 width=4) (actual
time=0.009..0.009 rows=0 loops=1)
Join Filter: ((pri.id_prioridade_processo)::integer =
(pripro.id_prioridade_processo)::integer)
-> Index Scan using
tb_processo_prioridade_processo_unq on tb_proc_prioridde_processo pripro
(cost=0.00..8.27 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: ((id_processo_trf)::integer =
(proctrf.id_processo_trf)::integer)
-> Seq Scan on tb_prioridade_processo pri
(cost=0.00..1.03 rows=3 width=8) (never executed)
*Total runtime: 2917.356 ms*
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
