explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GI99

Settings
# exclusive inclusive rows x rows loops node
1. 1,463.169 60,410.256 ↓ 51.0 51 1

GroupAggregate (cost=18,030.23..18,030.38 rows=1 width=54) (actual time=59,181.918..60,410.256 rows=51 loops=1)

  • Group Key: patbemloc.cd_filial, pes.no_reduzido
2. 558.216 58,947.087 ↓ 757,870.0 757,870 1

Sort (cost=18,030.23..18,030.24 rows=1 width=54) (actual time=58,886.617..58,947.087 rows=757,870 loops=1)

  • Sort Key: patbemloc.cd_filial, pes.no_reduzido
  • Sort Method: quicksort Memory: 84687kB
3. 276.297 58,388.871 ↓ 757,870.0 757,870 1

Nested Loop (cost=15,597.97..18,030.22 rows=1 width=54) (actual time=755.331..58,388.871 rows=757,870 loops=1)

4. 459.005 56,596.834 ↓ 757,870.0 757,870 1

Nested Loop Anti Join (cost=15,597.83..18,030.06 rows=1 width=50) (actual time=755.292..56,596.834 rows=757,870 loops=1)

5. 345.565 52,346.259 ↓ 252,771.3 758,314 1

Nested Loop (cost=15,597.41..18,028.69 rows=3 width=74) (actual time=755.267..52,346.259 rows=758,314 loops=1)

  • Join Filter: (patbem.cd_bem = patbemmov.cd_bem)
6. 679.592 49,347.010 ↓ 16,181.0 16,181 1

Nested Loop (cost=15,596.98..18,025.53 rows=1 width=69) (actual time=755.201..49,347.010 rows=16,181 loops=1)

  • Join Filter: ((patbem.cd_bem = patbemloc.cd_bem) AND (pesfil.cd_filial = patbemloc.cd_filial))
  • Rows Removed by Join Filter: 1019403
7. 3,159.843 23,813.402 ↓ 2,703.9 1,035,584 1

Nested Loop (cost=15,596.56..17,600.42 rows=383 width=93) (actual time=754.656..23,813.402 rows=1,035,584 loops=1)

8. 517.354 4,206.839 ↓ 2,232.2 2,741,120 1

Nested Loop (cost=15,596.27..16,929.94 rows=1,228 width=97) (actual time=754.556..4,206.839 rows=2,741,120 loops=1)

9. 0.312 7.245 ↓ 64.0 64 1

Nested Loop (cost=0.85..454.40 rows=1 width=49) (actual time=0.144..7.245 rows=64 loops=1)

10. 0.378 5.717 ↓ 64.0 64 1

Nested Loop (cost=0.56..453.95 rows=1 width=44) (actual time=0.130..5.717 rows=64 loops=1)

11. 0.328 3.035 ↓ 64.0 64 1

Nested Loop (cost=0.56..450.80 rows=1 width=32) (actual time=0.086..3.035 rows=64 loops=1)

12. 0.339 0.339 ↑ 1.0 64 1

Index Scan using pessoa_filial_pk on pessoa_filial pesfil (cost=0.14..30.32 rows=64 width=31) (actual time=0.026..0.339 rows=64 loops=1)

  • Index Cond: (cd_empresa = 1)
13. 2.368 2.368 ↑ 1.0 1 64

Index Only Scan using pessoa_atividade_pk on pessoa_atividade pesati (cost=0.41..6.56 rows=1 width=15) (actual time=0.034..0.037 rows=1 loops=64)

  • Index Cond: ((cd_pessoa = pesfil.cd_pessoa) AND (id_atividade = (pesfil.id_atividade)::text))
  • Heap Fetches: 63
14. 2.304 2.304 ↑ 1.0 1 64

Seq Scan on pat_grupo patgru (cost=0.00..3.14 rows=1 width=12) (actual time=0.028..0.036 rows=1 loops=64)

  • Filter: ((cd_empresa = 1) AND ((cd_pat_grupo)::text = '003'::text))
  • Rows Removed by Filter: 76
15. 1.216 1.216 ↑ 1.0 1 64

Index Scan using pessoa_pk on pessoa pes (cost=0.29..0.44 rows=1 width=29) (actual time=0.018..0.019 rows=1 loops=64)

  • Index Cond: (cd_pessoa = pesati.cd_pessoa)
16. 3,346.777 3,682.240 ↓ 1.5 42,830 64

HashAggregate (cost=15,595.42..15,888.79 rows=29,337 width=27) (actual time=11.789..57.535 rows=42,830 loops=64)

  • Group Key: _loc.cd_empresa, _loc.cd_bem
17. 62.667 335.463 ↓ 1.0 151,700 1

Hash Anti Join (cost=5,527.08..13,420.35 rows=145,005 width=27) (actual time=91.052..335.463 rows=151,700 loops=1)

  • Hash Cond: ((_loc.cd_empresa = _canc.cd_empresa) AND (_loc.cd_base = _canc.cd_base) AND (_loc.seq_processo = _canc.seq_processo))
18. 182.353 182.353 ↓ 1.0 152,062 1

Seq Scan on pat_bem_local _loc (cost=0.00..5,233.69 rows=151,015 width=43) (actual time=0.019..182.353 rows=152,062 loops=1)

  • Filter: ((cd_empresa = 1) AND (dt_alt_local <= to_date('2019-01-31'::text, 'yyyy-MM-dd'::text)))
  • Rows Removed by Filter: 13
19. 42.092 90.443 ↑ 1.0 129,270 1

Hash (cost=3,263.70..3,263.70 rows=129,336 width=24) (actual time=90.443..90.443 rows=129,270 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8094kB
20. 48.351 48.351 ↑ 1.0 129,270 1

Seq Scan on processo_canc _canc (cost=0.00..3,263.70 rows=129,336 width=24) (actual time=0.031..48.351 rows=129,270 loops=1)

  • Filter: (cd_empresa = 1)
21. 16,446.720 16,446.720 ↓ 0.0 0 2,741,120

Index Scan using pat_bem_pk on pat_bem patbem (cost=0.29..0.54 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=2,741,120)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = _loc.cd_bem))
  • Filter: (((cd_pat_grupo)::text = '003'::text) AND ((dt_baixa > to_date('2019-01-31'::text, 'yyyy-MM-dd'::text)) OR (id_situacao <> ALL ('{B,L,C}'::bpchar[]))))
  • Rows Removed by Filter: 1
22. 24,854.016 24,854.016 ↑ 1.0 1 1,035,584

Index Scan using pat_bem_local_pk on pat_bem_local patbemloc (cost=0.42..1.09 rows=1 width=35) (actual time=0.023..0.024 rows=1 loops=1,035,584)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = _loc.cd_bem))
  • Filter: ((max((to_char((_loc.dt_alt_local)::timestamp with time zone, 'YYYYMMDD'::text) || (_loc.hr_alt_local)::text))) = (to_char((dt_alt_local)::timestamp with time zone, 'YYYYMMDD'::text) || (hr_alt_local)::text))
  • Rows Removed by Filter: 4
23. 2,653.684 2,653.684 ↑ 1.1 47 16,181

Index Scan using pat_bem_mov_idx2 on pat_bem_mov patbemmov (cost=0.43..2.50 rows=53 width=53) (actual time=0.019..0.164 rows=47 loops=16,181)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = patbemloc.cd_bem))
  • Filter: (dt_movimento <= to_date('2019-01-31'::text, 'yyyy-MM-dd'::text))
  • Rows Removed by Filter: 0
24. 3,791.570 3,791.570 ↓ 0.0 0 758,314

Index Only Scan using processo_canc_pk on processo_canc procan (cost=0.42..0.45 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=758,314)

  • Index Cond: ((cd_empresa = patbemmov.cd_empresa) AND (cd_empresa = 1) AND (cd_base = patbemmov.cd_base) AND (seq_processo = patbemmov.seq_processo))
  • Heap Fetches: 124
25. 1,515.740 1,515.740 ↑ 1.0 1 757,870

Index Scan using operacao_patrimonio_pk on operacao_patrimonio opepat (cost=0.14..0.16 rows=1 width=20) (actual time=0.001..0.002 rows=1 loops=757,870)

  • Index Cond: (cd_operacao = patbemmov.cd_operacao)