explain.depesz.com

PostgreSQL's explain analyze made readable

Result: haf0

Settings
# exclusive inclusive rows x rows loops node
1. 1,529.583 59,347.332 ↓ 51.0 51 1

GroupAggregate (cost=18,030.23..18,030.38 rows=1 width=54) (actual time=58,040.884..59,347.332 rows=51 loops=1)

  • Group Key: patbemloc.cd_filial, pes.no_reduzido
2. 566.308 57,817.749 ↓ 757,870.0 757,870 1

Sort (cost=18,030.23..18,030.24 rows=1 width=54) (actual time=57,753.961..57,817.749 rows=757,870 loops=1)

  • Sort Key: patbemloc.cd_filial, pes.no_reduzido
  • Sort Method: quicksort Memory: 84687kB
3. 219.880 57,251.441 ↓ 757,870.0 757,870 1

Nested Loop (cost=15,597.97..18,030.22 rows=1 width=54) (actual time=785.220..57,251.441 rows=757,870 loops=1)

4. 409.319 55,515.821 ↓ 757,870.0 757,870 1

Nested Loop Anti Join (cost=15,597.83..18,030.06 rows=1 width=50) (actual time=785.193..55,515.821 rows=757,870 loops=1)

5. 321.108 51,314.932 ↓ 252,771.3 758,314 1

Nested Loop (cost=15,597.41..18,028.69 rows=3 width=74) (actual time=785.167..51,314.932 rows=758,314 loops=1)

  • Join Filter: (patbem.cd_bem = patbemmov.cd_bem)
6. 1,058.138 48,259.235 ↓ 16,181.0 16,181 1

Nested Loop (cost=15,596.98..18,025.53 rows=1 width=69) (actual time=785.103..48,259.235 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. 2,751.316 23,382.665 ↓ 2,703.9 1,035,584 1

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

8. 495.398 4,184.629 ↓ 2,232.2 2,741,120 1

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

9. 0.283 6.095 ↓ 64.0 64 1

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

10. 0.476 4.916 ↓ 64.0 64 1

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

11. 0.402 2.264 ↓ 64.0 64 1

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

12. 0.326 0.326 ↑ 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.024..0.326 rows=64 loops=1)

  • Index Cond: (cd_empresa = 1)
13. 1.536 1.536 ↑ 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.022..0.024 rows=1 loops=64)

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

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

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

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

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

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

  • Group Key: _loc.cd_empresa, _loc.cd_bem
17. 64.889 359.957 ↓ 1.0 151,700 1

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

  • Hash Cond: ((_loc.cd_empresa = _can.cd_empresa) AND (_loc.cd_base = _can.cd_base) AND (_loc.seq_processo = _can.seq_processo))
18. 184.523 184.523 ↓ 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.026..184.523 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. 50.838 110.545 ↑ 1.0 129,270 1

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

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

Seq Scan on processo_canc _can (cost=0.00..3,263.70 rows=129,336 width=24) (actual time=0.020..59.707 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. 23,818.432 23,818.432 ↑ 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.023 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,734.589 2,734.589 ↑ 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.018..0.169 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)