explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GzI

Settings
# exclusive inclusive rows x rows loops node
1. 1,621.699 41,138.599 ↓ 51.0 51 1

GroupAggregate (cost=1,669,123.17..1,669,123.32 rows=1 width=54) (actual time=39,726.008..41,138.599 rows=51 loops=1)

  • Group Key: patbemloc.cd_filial, pes.no_reduzido
2. 706.743 39,516.900 ↓ 747,271.0 747,271 1

Sort (cost=1,669,123.17..1,669,123.18 rows=1 width=54) (actual time=39,439.336..39,516.900 rows=747,271 loops=1)

  • Sort Key: patbemloc.cd_filial, pes.no_reduzido
  • Sort Method: quicksort Memory: 83844kB
3. 815.372 38,810.157 ↓ 747,271.0 747,271 1

Nested Loop Anti Join (cost=86.63..1,669,123.16 rows=1 width=54) (actual time=197.282..38,810.157 rows=747,271 loops=1)

4. 958.544 33,508.495 ↓ 19,676.7 747,715 1

Nested Loop (cost=86.21..1,669,105.90 rows=38 width=78) (actual time=185.679..33,508.495 rows=747,715 loops=1)

5. 304.989 31,054.521 ↓ 19,676.7 747,715 1

Nested Loop (cost=86.08..1,669,099.63 rows=38 width=74) (actual time=185.606..31,054.521 rows=747,715 loops=1)

6. 43.105 10,451.034 ↓ 16,187.0 16,187 1

Nested Loop (cost=85.65..1,669,093.55 rows=1 width=61) (actual time=175.905..10,451.034 rows=16,187 loops=1)

7. 31.582 10,165.124 ↓ 16,187.0 16,187 1

Nested Loop (cost=85.36..1,669,093.17 rows=1 width=56) (actual time=175.833..10,165.124 rows=16,187 loops=1)

8. 190.468 9,987.859 ↓ 16,187.0 16,187 1

Nested Loop (cost=85.21..1,669,090.99 rows=1 width=60) (actual time=170.592..9,987.859 rows=16,187 loops=1)

  • Join Filter: (patbemloc.cd_filial = pesfil.cd_filial)
  • Rows Removed by Join Filter: 1019781
9. 59.081 9,489.838 ↓ 68.6 16,187 1

Nested Loop (cost=0.71..1,668,338.88 rows=236 width=44) (actual time=34.131..9,489.838 rows=16,187 loops=1)

10. 1,337.257 1,337.257 ↓ 1.2 16,187 1

Index Scan using pat_bem_pk on pat_bem patbem (cost=0.29..3,618.65 rows=13,337 width=20) (actual time=3.806..1,337.257 rows=16,187 loops=1)

  • Index Cond: (cd_empresa = 1)
  • Filter: (((cd_pat_grupo)::text = '003'::text) AND ((dt_baixa > to_date('2018-12-31'::text, 'yyyy-MM-dd'::text)) OR (id_situacao <> ALL ('{B,L,C}'::bpchar[]))))
  • Rows Removed by Filter: 26532
11. 1,437.892 8,093.500 ↑ 1.0 1 16,187

Index Scan using pat_bem_local_pk on pat_bem_local patbemloc (cost=0.42..124.81 rows=1 width=24) (actual time=0.499..0.500 rows=1 loops=16,187)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = patbem.cd_bem))
  • Filter: ((to_char((dt_alt_local)::timestamp with time zone, 'YYYYMMDD'::text) || (hr_alt_local)::text) = (SubPlan 1))
  • Rows Removed by Filter: 4
12.          

SubPlan (forIndex Scan)

13. 1,972.032 6,655.608 ↑ 1.0 1 82,168

Aggregate (cost=24.72..24.73 rows=1 width=11) (actual time=0.081..0.081 rows=1 loops=82,168)

14. 754.168 4,683.576 ↓ 1.2 6 82,168

Nested Loop Anti Join (cost=0.84..24.67 rows=5 width=11) (actual time=0.011..0.057 rows=6 loops=82,168)

15. 2,875.880 2,875.880 ↓ 1.2 6 82,168

Index Scan using pat_bem_local_pk on pat_bem_local patbemlocaux (cost=0.42..8.04 rows=5 width=35) (actual time=0.006..0.035 rows=6 loops=82,168)

  • Index Cond: ((cd_empresa = patbemloc.cd_empresa) AND (cd_bem = patbemloc.cd_bem) AND (dt_alt_local <= to_date('2018-12-31'::text, 'yyyy-MM-dd'::text)))
16. 1,053.528 1,053.528 ↓ 0.0 0 526,764

Index Only Scan using processo_canc_pk on processo_canc procan_1 (cost=0.42..2.84 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=526,764)

  • Index Cond: ((cd_empresa = patbemlocaux.cd_empresa) AND (cd_empresa = patbemloc.cd_empresa) AND (cd_base = patbemlocaux.cd_base) AND (seq_processo = patbemlocaux.seq_processo))
  • Heap Fetches: 278
17. 103.928 307.553 ↓ 64.0 64 16,187

Materialize (cost=84.50..748.58 rows=1 width=32) (actual time=0.006..0.019 rows=64 loops=16,187)

18. 0.315 203.625 ↓ 64.0 64 1

Nested Loop (cost=84.50..748.57 rows=1 width=32) (actual time=90.776..203.625 rows=64 loops=1)

19. 0.545 83.630 ↑ 3.6 64 1

HashAggregate (cost=84.09..86.38 rows=229 width=211) (actual time=83.399..83.630 rows=64 loops=1)

  • Group Key: pesfil.cd_empresa, pesfil.cd_filial, pesfil.id_atividade, pesfil.cd_pessoa, pesfil.cd_segmento_fiscal, pesfil.cd_lista_preco_basica, pesfil.cd_pessoa_clas_fiscal, pesfil.id_perfil_sped_fiscal, pesfil.id_periodo_inventario, pesfil.mes_inventario_anual, pesfil.versao_leiaute_nfe, pesfil.id_formato_danfe, pesfil.id_ambiente_nfe, pesfil.cd_operacao_venda, pesfil.id_volume_trans, pesfil.id_utiliza_lacre_transp, pesfil.cd_tipo_cobranca, pesfil.cd_banco_portador, pesfil.nr_agencia_portador, pesfil.nr_conta_portador, pesfil.id_clas_cons_en_eletr, pesfil.id_tipo_ligacao_default, pesfil.id_grupo_tensao_default, pesfil.id_clas_cons_gas, pesfil.id_tipo_assinante_comunic, pesfil.id_clas_cons_agua, pesfil.cd_lista_preco_tipo_basico, pesfil.cd_lista_preco_prom, pesfil.cd_lista_preco_tipo_prom, pesfil.cd_grupo_unidade_medida, pesfil.id_ecf, pesfil.id_tipo_atualizacao_est, pesfil.id_atualizando_est, pesfil.dt_ult_fec_estoque, pesfil.mes_invent_sped_anual, pesfil.id_emite_nf_conjugada, pesfil.id_origem_tipo_preco, pesfil.cd_operacao_serv, pesfil.id_obriga_cd_barras_nfe, pesfil.id_ativo, pesfil.abrev_filial, pesfil.id_geracao_nfe, pesfil.id_matriz, pesfil.cd_perfil_filial, pesfil.id_venda_saldo_terc
20. 0.014 83.085 ↑ 3.6 64 1

Append (cost=14.79..58.33 rows=229 width=211) (actual time=82.856..83.085 rows=64 loops=1)

21. 0.000 0.943 ↓ 0.0 0 1

Nested Loop (cost=14.79..23.70 rows=36 width=211) (actual time=0.943..0.943 rows=0 loops=1)

22. 0.001 0.943 ↓ 0.0 0 1

Nested Loop (cost=0.70..9.09 rows=12 width=211) (actual time=0.943..0.943 rows=0 loops=1)

23. 0.002 0.942 ↓ 0.0 0 1

Nested Loop (cost=0.56..7.00 rows=12 width=16) (actual time=0.942..0.942 rows=0 loops=1)

24. 0.940 0.940 ↓ 0.0 0 1

Index Scan using operador_uk1 on operador ope (cost=0.28..4.30 rows=1 width=9) (actual time=0.940..0.940 rows=0 loops=1)

  • Index Cond: ((seq_operador)::text = '0193'::text)
  • Filter: (id_visao_filial_todos = 'N'::bpchar)
  • Rows Removed by Filter: 1
25. 0.000 0.000 ↓ 0.0 0

Index Only Scan using operador_pessoa_filial_pk on operador_pessoa_filial opepesfil (cost=0.28..2.56 rows=14 width=23) (never executed)

  • Index Cond: ((cd_operador = (ope.cd_operador)::text) AND (cd_empresa = 1))
  • Heap Fetches: 0
26. 0.000 0.000 ↓ 0.0 0

Index Scan using pessoa_filial_pk on pessoa_filial pesfil (cost=0.14..0.16 rows=1 width=211) (never executed)

  • Index Cond: ((cd_empresa = 1) AND (cd_filial = opepesfil.cd_filial))
27. 0.000 0.000 ↓ 0.0 0

Materialize (cost=14.09..14.16 rows=3 width=8) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Subquery Scan on pesemp (cost=14.09..14.14 rows=3 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=14.09..14.12 rows=3 width=112) (never executed)

  • Group Key: pesemp_1.cd_empresa, pesemp_1.cd_pessoa, pesemp_1.cd_regime_fiscal, pesemp_1.dt_baixa, pesemp_1.dt_arq_atos_const, pesemp_1.dt_arq_conv_simp_empr, pesemp_1.coef_acresc_venda_ncoop, pesemp_1.id_cooperativa, pesemp_1.id_origem_tribut, pesemp_1.id_controla_fci, pesemp_1.nr_max_itens_nfe, pesemp_1.id_valida_cest_fat, pesemp_1.id_consiste_ncm_xml, pesemp_1.nr_dias_rev_cad_cli_ven
30. 0.000 0.000 ↓ 0.0 0

Append (cost=0.55..13.98 rows=3 width=112) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..7.62 rows=1 width=112) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..6.60 rows=1 width=8) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using operador_uk1 on operador ope_1 (cost=0.28..4.30 rows=1 width=9) (never executed)

  • Index Cond: ((seq_operador)::text = '0193'::text)
  • Filter: (id_visao_emp_todos = 'N'::bpchar)
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using operador_pessoa_empresa_pk on operador_pessoa_empresa opepesemp (cost=0.28..2.29 rows=1 width=17) (never executed)

  • Index Cond: ((cd_operador = (ope_1.cd_operador)::text) AND (cd_empresa = 1))
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on pessoa_empresa pesemp_1 (cost=0.00..1.01 rows=1 width=112) (never executed)

  • Filter: (cd_empresa = 1)
36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..5.32 rows=1 width=112) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using operador_uk1 on operador ope_2 (cost=0.28..4.30 rows=1 width=0) (never executed)

  • Index Cond: ((seq_operador)::text = '0193'::text)
  • Filter: (id_visao_emp_todos = 'S'::bpchar)
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on pessoa_empresa pesemp_2 (cost=0.00..1.01 rows=1 width=112) (never executed)

  • Filter: (cd_empresa = 1)
39. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..1.01 rows=1 width=112) (never executed)

  • One-Time Filter: false
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on pessoa_empresa pesemp_3 (cost=0.00..1.01 rows=1 width=112) (never executed)

41. 0.011 82.127 ↑ 3.0 64 1

Nested Loop (cost=14.36..26.69 rows=192 width=211) (actual time=81.910..82.127 rows=64 loops=1)

42. 0.196 0.196 ↑ 1.0 64 1

Seq Scan on pessoa_filial pesfil_1 (cost=0.00..5.81 rows=64 width=211) (actual time=0.030..0.196 rows=64 loops=1)

  • Filter: (cd_empresa = 1)
  • Rows Removed by Filter: 1
43. 0.047 81.920 ↑ 3.0 1 64

Materialize (cost=14.36..18.48 rows=3 width=8) (actual time=1.279..1.280 rows=1 loops=64)

44. 0.004 81.873 ↑ 3.0 1 1

Nested Loop (cost=14.36..18.47 rows=3 width=8) (actual time=81.870..81.873 rows=1 loops=1)

45. 0.021 0.021 ↑ 1.0 1 1

Index Scan using operador_uk1 on operador ope_3 (cost=0.28..4.30 rows=1 width=0) (actual time=0.019..0.021 rows=1 loops=1)

  • Index Cond: ((seq_operador)::text = '0193'::text)
  • Filter: (id_visao_filial_todos = 'S'::bpchar)
46. 0.024 81.848 ↑ 3.0 1 1

HashAggregate (cost=14.09..14.12 rows=3 width=112) (actual time=81.847..81.848 rows=1 loops=1)

  • Group Key: pesemp_4.cd_empresa, pesemp_4.cd_pessoa, pesemp_4.cd_regime_fiscal, pesemp_4.dt_baixa, pesemp_4.dt_arq_atos_const, pesemp_4.dt_arq_conv_simp_empr, pesemp_4.coef_acresc_venda_ncoop, pesemp_4.id_cooperativa, pesemp_4.id_origem_tribut, pesemp_4.id_controla_fci, pesemp_4.nr_max_itens_nfe, pesemp_4.id_valida_cest_fat, pesemp_4.id_consiste_ncm_xml, pesemp_4.nr_dias_rev_cad_cli_ven
47. 0.004 81.824 ↑ 3.0 1 1

Append (cost=0.55..13.98 rows=3 width=112) (actual time=81.768..81.824 rows=1 loops=1)

48. 0.015 81.776 ↑ 1.0 1 1

Nested Loop (cost=0.55..7.62 rows=1 width=112) (actual time=81.766..81.776 rows=1 loops=1)

49. 0.017 4.541 ↑ 1.0 1 1

Nested Loop (cost=0.55..6.60 rows=1 width=8) (actual time=4.533..4.541 rows=1 loops=1)

50. 0.019 0.019 ↑ 1.0 1 1

Index Scan using operador_uk1 on operador ope_4 (cost=0.28..4.30 rows=1 width=9) (actual time=0.016..0.019 rows=1 loops=1)

  • Index Cond: ((seq_operador)::text = '0193'::text)
  • Filter: (id_visao_emp_todos = 'N'::bpchar)
51. 4.505 4.505 ↑ 1.0 1 1

Index Only Scan using operador_pessoa_empresa_pk on operador_pessoa_empresa opepesemp_1 (cost=0.28..2.29 rows=1 width=17) (actual time=4.500..4.505 rows=1 loops=1)

  • Index Cond: ((cd_operador = (ope_4.cd_operador)::text) AND (cd_empresa = 1))
  • Heap Fetches: 0
52. 77.220 77.220 ↑ 1.0 1 1

Seq Scan on pessoa_empresa pesemp_4 (cost=0.00..1.01 rows=1 width=112) (actual time=77.219..77.220 rows=1 loops=1)

  • Filter: (cd_empresa = 1)
  • Rows Removed by Filter: 1
53. 0.000 0.043 ↓ 0.0 0 1

Nested Loop (cost=0.28..5.32 rows=1 width=112) (actual time=0.043..0.043 rows=0 loops=1)

54. 0.043 0.043 ↓ 0.0 0 1

Index Scan using operador_uk1 on operador ope_5 (cost=0.28..4.30 rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1)

  • Index Cond: ((seq_operador)::text = '0193'::text)
  • Filter: (id_visao_emp_todos = 'S'::bpchar)
  • Rows Removed by Filter: 1
55. 0.000 0.000 ↓ 0.0 0

Seq Scan on pessoa_empresa pesemp_5 (cost=0.00..1.01 rows=1 width=112) (never executed)

  • Filter: (cd_empresa = 1)
56. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..1.01 rows=1 width=112) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on pessoa_empresa pesemp_6 (cost=0.00..1.01 rows=1 width=112) (never executed)

58. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..5.65 rows=1 width=211) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on pessoa_filial pesfil_2 (cost=0.00..5.65 rows=1 width=211) (never executed)

60. 119.680 119.680 ↑ 1.0 1 64

Index Only Scan using pessoa_atividade_pk on pessoa_atividade pesati (cost=0.41..2.87 rows=1 width=15) (actual time=1.868..1.870 rows=1 loops=64)

  • Index Cond: ((cd_pessoa = pesfil.cd_pessoa) AND (id_atividade = (pesfil.id_atividade)::text))
  • Heap Fetches: 63
61. 145.683 145.683 ↑ 1.0 1 16,187

Index Only Scan using pat_grupo_pk on pat_grupo patgru (cost=0.14..2.16 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=16,187)

  • Index Cond: ((cd_empresa = 1) AND (cd_pat_grupo = '003'::text))
  • Heap Fetches: 0
62. 242.805 242.805 ↑ 1.0 1 16,187

Index Scan using pessoa_pk on pessoa pes (cost=0.29..0.38 rows=1 width=29) (actual time=0.012..0.015 rows=1 loops=16,187)

  • Index Cond: (cd_pessoa = pesati.cd_pessoa)
63. 20,298.498 20,298.498 ↑ 1.2 46 16,187

Index Scan using pat_bem_mov_idx2 on pat_bem_mov patbemmov (cost=0.43..5.55 rows=53 width=53) (actual time=0.141..1.254 rows=46 loops=16,187)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = patbem.cd_bem))
  • Filter: (dt_movimento <= to_date('2018-12-31'::text, 'yyyy-MM-dd'::text))
  • Rows Removed by Filter: 0
64. 1,495.430 1,495.430 ↑ 1.0 1 747,715

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=747,715)

  • Index Cond: (cd_operacao = patbemmov.cd_operacao)
65. 4,486.290 4,486.290 ↓ 0.0 0 747,715

Index Only Scan using processo_canc_pk on processo_canc procan (cost=0.42..0.44 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=747,715)

  • 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