explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HQrG

Settings
# exclusive inclusive rows x rows loops node
1. 1,402.693 66,072.456 ↓ 51.0 51 1

GroupAggregate (cost=9,934.25..9,934.39 rows=1 width=54) (actual time=64,879.808..66,072.456 rows=51 loops=1)

  • Group Key: patbemloc.cd_filial, pes.no_reduzido
2. 587.894 64,669.763 ↓ 756,320.0 756,320 1

Sort (cost=9,934.25..9,934.25 rows=1 width=54) (actual time=64,612.881..64,669.763 rows=756,320 loops=1)

  • Sort Key: patbemloc.cd_filial, pes.no_reduzido
  • Sort Method: quicksort Memory: 84566kB
3. 617.293 64,081.869 ↓ 756,320.0 756,320 1

Nested Loop Anti Join (cost=7,501.88..9,934.24 rows=1 width=54) (actual time=610.599..64,081.869 rows=756,320 loops=1)

4. 399.001 62,706.706 ↓ 757,870.0 757,870 1

Nested Loop (cost=7,501.46..9,933.72 rows=1 width=78) (actual time=610.585..62,706.706 rows=757,870 loops=1)

5. 654.342 60,791.965 ↓ 757,870.0 757,870 1

Nested Loop Anti Join (cost=7,501.32..9,933.55 rows=1 width=74) (actual time=610.549..60,791.965 rows=757,870 loops=1)

6. 361.777 56,346.053 ↓ 252,771.3 758,314 1

Nested Loop (cost=7,500.91..9,932.18 rows=3 width=98) (actual time=610.524..56,346.053 rows=758,314 loops=1)

  • Join Filter: (patbem.cd_bem = patbemmov.cd_bem)
7. 1,077.477 50,191.478 ↓ 16,181.0 16,181 1

Nested Loop (cost=7,500.48..9,929.02 rows=1 width=85) (actual time=609.982..50,191.478 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
8. 900.080 24,259.985 ↓ 2,703.9 1,035,584 1

Nested Loop (cost=7,500.06..9,503.91 rows=383 width=93) (actual time=609.427..24,259.985 rows=1,035,584 loops=1)

9. 512.816 4,172.065 ↓ 2,232.2 2,741,120 1

Nested Loop (cost=7,499.77..8,833.43 rows=1,228 width=97) (actual time=609.341..4,172.065 rows=2,741,120 loops=1)

10. 0.251 6.641 ↓ 64.0 64 1

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

11. 0.451 5.110 ↓ 64.0 64 1

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

12. 0.410 2.355 ↓ 64.0 64 1

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

13. 0.345 0.345 ↑ 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.018..0.345 rows=64 loops=1)

  • Index Cond: (cd_empresa = 1)
14. 1.600 1.600 ↑ 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.023..0.025 rows=1 loops=64)

  • Index Cond: ((cd_pessoa = pesfil.cd_pessoa) AND (id_atividade = (pesfil.id_atividade)::text))
  • Heap Fetches: 63
15. 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
16. 1.280 1.280 ↑ 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.020 rows=1 loops=64)

  • Index Cond: (cd_pessoa = pesati.cd_pessoa)
17. 3,462.063 3,652.608 ↓ 1.5 42,830 64

HashAggregate (cost=7,498.92..7,792.29 rows=29,337 width=27) (actual time=9.520..57.072 rows=42,830 loops=64)

  • Group Key: pat_bem_local.cd_empresa, pat_bem_local.cd_bem
18. 190.545 190.545 ↓ 1.0 152,062 1

Seq Scan on pat_bem_local (cost=0.00..5,233.69 rows=151,015 width=27) (actual time=0.021..190.545 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. 19,187.840 19,187.840 ↓ 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.007..0.007 rows=0 loops=2,741,120)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = pat_bem_local.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
20. 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=51) (actual time=0.024..0.024 rows=1 loops=1,035,584)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = pat_bem_local.cd_bem))
  • Filter: ((max((to_char((pat_bem_local.dt_alt_local)::timestamp with time zone, 'YYYYMMDD'::text) || (pat_bem_local.hr_alt_local)::text))) = (to_char((dt_alt_local)::timestamp with time zone, 'YYYYMMDD'::text) || (hr_alt_local)::text))
  • Rows Removed by Filter: 4
21. 5,792.798 5,792.798 ↑ 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.137..0.358 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
22. 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
23. 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.002..0.002 rows=1 loops=757,870)

  • Index Cond: (cd_operacao = patbemmov.cd_operacao)
24. 757.870 757.870 ↓ 0.0 0 757,870

Index Only Scan using processo_canc_pk on processo_canc procan_1 (cost=0.42..0.47 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=757,870)

  • Index Cond: ((cd_empresa = patbemloc.cd_empresa) AND (cd_empresa = 1) AND (cd_base = patbemloc.cd_base) AND (seq_processo = patbemloc.seq_processo))
  • Heap Fetches: 654