explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LTvi : Optimization for: plan #MS5e

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 4,920.562 ↓ 0.0 0 1

Limit (cost=396,119.90..396,128.95 rows=3,619 width=124) (actual time=4,920.562..4,920.562 rows=0 loops=1)

2. 0.037 4,920.562 ↓ 0.0 0 1

Sort (cost=396,119.90..396,128.95 rows=3,619 width=124) (actual time=4,920.562..4,920.562 rows=0 loops=1)

  • Sort Key: estdocite.cd_empresa, estdocite.cd_filial_utiliz, estdoc.dt_movimento, opeest.id_entrada_saida
  • Sort Method: quicksort Memory: 25kB
3. 0.001 4,920.525 ↓ 0.0 0 1

Append (cost=1.16..395,869.80 rows=3,619 width=46) (actual time=4,920.525..4,920.525 rows=0 loops=1)

4. 0.001 4,920.485 ↓ 0.0 0 1

Result (cost=1.16..395,852.32 rows=3,618 width=46) (actual time=4,920.485..4,920.485 rows=0 loops=1)

5. 0.003 4,920.484 ↓ 0.0 0 1

Append (cost=1.16..395,852.32 rows=3,618 width=46) (actual time=4,920.484..4,920.484 rows=0 loops=1)

6. 0.001 0.194 ↓ 0.0 0 1

Nested Loop (cost=1.16..27.54 rows=1 width=46) (actual time=0.194..0.194 rows=0 loops=1)

  • Join Filter: (estdocite.cd_filial_utiliz = pesfil.cd_filial)
7. 0.000 0.193 ↓ 0.0 0 1

Nested Loop (cost=1.01..26.41 rows=1 width=54) (actual time=0.193..0.193 rows=0 loops=1)

8. 0.020 0.193 ↓ 0.0 0 1

Nested Loop (cost=0.87..18.24 rows=1 width=60) (actual time=0.193..0.193 rows=0 loops=1)

9. 0.063 0.063 ↓ 22.0 22 1

Index Scan using est_doc_item_idx2 on est_doc_item estdocite (cost=0.43..9.76 rows=1 width=48) (actual time=0.022..0.063 rows=22 loops=1)

  • Index Cond: (id_atualizado = ANY ('{Q,N}'::bpchar[]))
10. 0.110 0.110 ↓ 0.0 0 22

Index Scan using est_documento_pk on est_documento estdoc (cost=0.43..8.47 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=22)

  • Index Cond: ((cd_empresa = estdocite.cd_empresa) AND (cd_base = estdocite.cd_base) AND (seq_processo = estdocite.seq_processo))
  • Filter: ((id_situacao_eletronica = ANY ('{0,9}'::bpchar[])) AND ((id_situacao_sped)::text = ANY ('{00,01,06,07}'::text[])) AND (dt_movimento < to_date('2019-05-10'::text, 'yyyy-MM-dd'::text)))
  • Rows Removed by Filter: 1
11. 0.000 0.000 ↓ 0.0 0

Index Scan using operacao_estoque_pk on operacao_estoque opeest (cost=0.15..8.17 rows=1 width=10) (never executed)

  • Index Cond: (cd_operacao = estdocite.cd_operacao)
12. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pessoa_filial_pk on pessoa_filial pesfil (cost=0.14..0.72 rows=33 width=16) (never executed)

  • Index Cond: (cd_empresa = estdoc.cd_empresa)
  • Heap Fetches: 0
13. 0.000 0.241 ↓ 0.0 0 1

Nested Loop (cost=1.16..30.59 rows=1 width=46) (actual time=0.241..0.241 rows=0 loops=1)

14. 0.000 0.241 ↓ 0.0 0 1

Nested Loop (cost=1.01..22.41 rows=1 width=52) (actual time=0.241..0.241 rows=0 loops=1)

15. 0.012 0.135 ↓ 22.0 22 1

Nested Loop (cost=0.58..13.93 rows=1 width=56) (actual time=0.057..0.135 rows=22 loops=1)

16. 0.035 0.035 ↓ 22.0 22 1

Index Scan using est_doc_item_idx2 on est_doc_item estdocite_1 (cost=0.43..9.76 rows=1 width=48) (actual time=0.009..0.035 rows=22 loops=1)

  • Index Cond: (id_atualizado = ANY ('{Q,N}'::bpchar[]))
17. 0.088 0.088 ↑ 1.0 1 22

Index Only Scan using pessoa_filial_pk on pessoa_filial pesfil_1 (cost=0.14..4.16 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=22)

  • Index Cond: ((cd_empresa = estdocite_1.cd_empresa) AND (cd_filial = estdocite_1.cd_filial_utiliz))
  • Heap Fetches: 22
18. 0.110 0.110 ↓ 0.0 0 22

Index Scan using est_documento_pk on est_documento estdoc_1 (cost=0.43..8.47 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=22)

  • Index Cond: ((cd_empresa = estdocite_1.cd_empresa) AND (cd_base = estdocite_1.cd_base) AND (seq_processo = estdocite_1.seq_processo))
  • Filter: ((id_situacao_eletronica = ANY ('{0,9}'::bpchar[])) AND ((hr_movimento)::text <= '020040'::text) AND ((id_situacao_sped)::text = ANY ('{00,01,06,07}'::text[])) AND (dt_movimento = to_date('2019-05-10'::text, 'yyyy-MM-dd'::text)))
  • Rows Removed by Filter: 1
19. 0.000 0.000 ↓ 0.0 0

Index Scan using operacao_estoque_pk on operacao_estoque opeest_1 (cost=0.15..8.17 rows=1 width=10) (never executed)

  • Index Cond: (cd_operacao = estdocite_1.cd_operacao)
  • Filter: (id_entrada_saida = 'E'::bpchar)
20. 0.000 0.126 ↓ 0.0 0 1

Nested Loop (cost=1.16..27.54 rows=1 width=46) (actual time=0.126..0.126 rows=0 loops=1)

  • Join Filter: (estdocite_2.cd_filial_utiliz = pesfil_2.cd_filial)
21. 0.000 0.126 ↓ 0.0 0 1

Nested Loop (cost=1.01..26.41 rows=1 width=54) (actual time=0.126..0.126 rows=0 loops=1)

22. 0.005 0.126 ↓ 0.0 0 1

Nested Loop (cost=0.87..18.23 rows=1 width=60) (actual time=0.126..0.126 rows=0 loops=1)

23. 0.033 0.033 ↓ 22.0 22 1

Index Scan using est_doc_item_idx2 on est_doc_item estdocite_2 (cost=0.43..9.76 rows=1 width=48) (actual time=0.009..0.033 rows=22 loops=1)

  • Index Cond: (id_atualizado = ANY ('{Q,N}'::bpchar[]))
24. 0.088 0.088 ↓ 0.0 0 22

Index Scan using est_documento_pk on est_documento estdoc_2 (cost=0.43..8.46 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=22)

  • Index Cond: ((cd_empresa = estdocite_2.cd_empresa) AND (cd_base = estdocite_2.cd_base) AND (seq_processo = estdocite_2.seq_processo))
  • Filter: ((id_situacao_eletronica = ANY ('{0,9}'::bpchar[])) AND ((id_situacao_sped)::text = ANY ('{00,01,06,07}'::text[])))
  • Rows Removed by Filter: 1
25. 0.000 0.000 ↓ 0.0 0

Index Scan using operacao_estoque_pk on operacao_estoque opeest_2 (cost=0.15..8.17 rows=1 width=10) (never executed)

  • Index Cond: (cd_operacao = estdocite_2.cd_operacao)
  • Filter: (id_entrada_saida = 'S'::bpchar)
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pessoa_filial_pk on pessoa_filial pesfil_2 (cost=0.14..0.72 rows=33 width=16) (never executed)

  • Index Cond: (cd_empresa = estdoc_2.cd_empresa)
  • Heap Fetches: 0
27. 0.010 4,919.920 ↓ 0.0 0 1

Hash Join (cost=231,271.46..395,730.47 rows=3,615 width=46) (actual time=4,919.920..4,919.920 rows=0 loops=1)

  • Hash Cond: ((estdoc_3.cd_empresa = pesfil_3.cd_empresa) AND (estdocite_3.cd_filial_utiliz = pesfil_3.cd_filial))
28. 0.006 4,919.859 ↓ 0.0 0 1

Hash Join (cost=231,264.81..395,653.71 rows=3,396 width=54) (actual time=4,919.859..4,919.859 rows=0 loops=1)

  • Hash Cond: (estdocite_3.cd_operacao = opeest_3.cd_operacao)
29. 51.702 4,919.655 ↓ 0.0 0 1

Hash Join (cost=231,248.40..395,590.60 rows=3,396 width=60) (actual time=4,919.655..4,919.655 rows=0 loops=1)

  • Hash Cond: ((estdocite_3.cd_empresa = estdoc_3.cd_empresa) AND (estdocite_3.cd_base = estdoc_3.cd_base) AND (estdocite_3.seq_processo = estdoc_3.seq_processo))
30. 188.406 188.406 ↑ 1.0 244,738 1

Index Scan using est_doc_item_idx2 on est_doc_item estdocite_3 (cost=0.43..161,552.59 rows=244,985 width=48) (actual time=0.033..188.406 rows=244,738 loops=1)

  • Index Cond: (id_atualizado = ANY ('{Q,N,F,S}'::bpchar[]))
  • Filter: (id_atualizado_canc IS NULL)
31. 50.130 4,679.547 ↓ 1.1 68,574 1

Hash (cost=230,203.53..230,203.53 rows=59,682 width=28) (actual time=4,679.547..4,679.547 rows=68,574 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 5310kB
32. 4,629.417 4,629.417 ↓ 1.1 68,574 1

Seq Scan on est_documento estdoc_3 (cost=0.00..230,203.53 rows=59,682 width=28) (actual time=0.067..4,629.417 rows=68,574 loops=1)

  • Filter: ((id_situacao_eletronica = ANY ('{0,9}'::bpchar[])) AND ((id_situacao_sped)::text = ANY ('{02,03,89}'::text[])))
  • Rows Removed by Filter: 4209102
33. 0.074 0.198 ↓ 1.0 376 1

Hash (cost=11.74..11.74 rows=374 width=10) (actual time=0.198..0.198 rows=376 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
34. 0.124 0.124 ↓ 1.0 376 1

Seq Scan on operacao_estoque opeest_3 (cost=0.00..11.74 rows=374 width=10) (actual time=0.007..0.124 rows=376 loops=1)

35. 0.022 0.051 ↑ 1.0 66 1

Hash (cost=5.66..5.66 rows=66 width=16) (actual time=0.051..0.051 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
36. 0.029 0.029 ↑ 1.0 66 1

Seq Scan on pessoa_filial pesfil_3 (cost=0.00..5.66 rows=66 width=16) (actual time=0.006..0.029 rows=66 loops=1)

37. 0.001 0.039 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=9.96..17.48 rows=1 width=156) (actual time=0.039..0.039 rows=0 loops=1)

38. 0.000 0.038 ↓ 0.0 0 1

Nested Loop (cost=9.96..17.47 rows=1 width=156) (actual time=0.038..0.038 rows=0 loops=1)

39. 0.002 0.038 ↓ 0.0 0 1

Nested Loop (cost=9.81..16.69 rows=1 width=162) (actual time=0.038..0.038 rows=0 loops=1)

  • Join Filter: ((ecfcupfisite.cd_empresa = ecfcupfis.cd_empresa) AND (ecfcupfisite.cd_filial = ecfcupfis.cd_filial) AND ((ecfcupfisite.nr_serie_fab_ecf)::text = (ecfcupfis.nr_serie_fab_ecf)::text) AND (ecfcupfisite.nr_cupom_fiscal = ecfcupfis.nr_cupom_fiscal))
40. 0.018 0.036 ↓ 0.0 0 1

Hash Join (cost=9.67..15.84 rows=1 width=174) (actual time=0.036..0.036 rows=0 loops=1)

  • Hash Cond: ((pesfil_4.cd_empresa = ecfcupfisite.cd_empresa) AND (pesfil_4.cd_filial = ecfcupfisite.cd_filial))
41. 0.009 0.009 ↑ 66.0 1 1

Seq Scan on pessoa_filial pesfil_4 (cost=0.00..5.66 rows=66 width=24) (actual time=0.009..0.009 rows=1 loops=1)

42. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=9.64..9.64 rows=2 width=150) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
43. 0.002 0.008 ↓ 0.0 0 1

Bitmap Heap Scan on ecf_cupom_fiscal_item ecfcupfisite (cost=4.30..9.64 rows=2 width=150) (actual time=0.008..0.008 rows=0 loops=1)

  • Recheck Cond: (id_atualizado = ANY ('{Q,N}'::bpchar[]))
44. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on ecf_cupom_fiscal_item_idx1 (cost=0.00..4.30 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (id_atualizado = ANY ('{Q,N}'::bpchar[]))
45. 0.000 0.000 ↓ 0.0 0

Index Scan using ecf_cupom_fiscal_idx1 on ecf_cupom_fiscal ecfcupfis (cost=0.14..0.83 rows=1 width=146) (never executed)

  • Index Cond: ((cd_empresa = pesfil_4.cd_empresa) AND (cd_filial = pesfil_4.cd_filial))
  • Filter: ((id_situacao_sped)::text = ANY ('{00,01}'::text[]))
46. 0.000 0.000 ↓ 0.0 0

Index Scan using operacao_estoque_pk on operacao_estoque opeest_4 (cost=0.15..0.77 rows=1 width=10) (never executed)

  • Index Cond: (cd_operacao = pesfil_4.cd_operacao_venda)
Planning time : 7,459.835 ms
Execution time : 4,921.351 ms