explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E6Cf

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 2,799.356 ↑ 11.3 31 1

WindowAgg (cost=808,935.62..809,149.83 rows=350 width=498) (actual time=2,799.345..2,799.356 rows=31 loops=1)

  • Functions: 183
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 44.136 ms, Inlining 32.792 ms, Optimization 1456.221 ms, Emission 947.922 ms, Total 2481.072 ms
2. 0.064 2,799.315 ↑ 11.3 31 1

GroupAggregate (cost=808,935.62..809,137.58 rows=350 width=458) (actual time=2,799.259..2,799.315 rows=31 loops=1)

  • Group Key: t.unn_ds_fantasia, t.tipo, t.cst, t.grupo, t.pis, t.cofins
3. 0.068 2,799.251 ↑ 97.1 36 1

Sort (cost=808,935.62..808,944.36 rows=3,497 width=394) (actual time=2,799.249..2,799.251 rows=36 loops=1)

  • Sort Key: t.unn_ds_fantasia, t.tipo, t.cst, t.grupo, t.pis, t.cofins
  • Sort Method: quicksort Memory: 30kB
4. 0.007 2,799.183 ↑ 97.1 36 1

Subquery Scan on t (cost=791,617.85..808,729.79 rows=3,497 width=394) (actual time=2,789.170..2,799.183 rows=36 loops=1)

5. 0.008 2,799.176 ↑ 97.1 36 1

Append (cost=791,617.85..808,694.82 rows=3,497 width=394) (actual time=2,789.168..2,799.176 rows=36 loops=1)

6. 0.003 2,789.179 ↑ 5.9 17 1

Subquery Scan on *SELECT* 1 (cost=791,617.85..791,622.60 rows=100 width=274) (actual time=2,789.166..2,789.179 rows=17 loops=1)

7. 46.914 2,789.176 ↑ 5.9 17 1

HashAggregate (cost=791,617.85..791,621.60 rows=100 width=274) (actual time=2,789.164..2,789.176 rows=17 loops=1)

  • Group Key: (vei.value ->> 'ptp_ds_cst_pis_saida'::text), COALESCE(((vei.value ->> 'ptp_vl_pr_pis_saida'::text))::numeric, '0'::numeric), COALESCE(((vei.value ->> 'ptp_vl_pr_cofins_saida'::text))::numeric, '0'::numeric), (vei.value ->> 'pgr_ds_produto_grupo'::text), 'S'::text, unn.unn_ds_fantasia
8. 44.531 2,742.262 ↑ 210.3 39,092 1

Nested Loop (cost=0.84..421,627.85 rows=8,222,000 width=210) (actual time=2,443.464..2,742.262 rows=39,092 loops=1)

9. 2,449.062 2,468.341 ↑ 2.5 32,770 1

Nested Loop (cost=0.84..92,747.84 rows=82,220 width=1,416) (actual time=2,443.422..2,468.341 rows=32,770 loops=1)

10. 0.018 0.018 ↑ 1.0 1 1

Index Scan using unidade_negocio_pkey on unidade_negocio unn (cost=0.28..2.49 rows=1 width=22) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
11. 19.261 19.261 ↑ 2.5 32,770 1

Index Scan using venda_ven_cd_unidade_negocio_ven_fl_cancelada_ven_dt_fiscal_ven on venda ven (cost=0.56..91,923.15 rows=82,220 width=1,402) (actual time=0.023..19.261 rows=32,770 loops=1)

  • Index Cond: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar) AND (ven_dt_fiscal >= '2019-11-01'::date) AND (ven_dt_fiscal <= '2019-12-01'::date))
12. 229.390 229.390 ↑ 100.0 1 32,770

Function Scan on jsonb_array_elements vei (cost=0.01..1.00 rows=100 width=32) (actual time=0.007..0.007 rows=1 loops=32,770)

13. 0.002 6.403 ↑ 365.5 6 1

Subquery Scan on *SELECT* 2 (cost=10,788.28..10,848.59 rows=2,193 width=216) (actual time=6.389..6.403 rows=6 loops=1)

14. 0.204 6.401 ↑ 365.5 6 1

HashAggregate (cost=10,788.28..10,826.66 rows=2,193 width=166) (actual time=6.387..6.401 rows=6 loops=1)

  • Group Key: nsi.nsi_ds_cst_pis, nsi.nsi_vl_pr_pis, nsi.nsi_vl_pr_cofins, pgr.pgr_ds_produto_grupo, 'S'::text, unn_1.unn_ds_fantasia
15. 0.059 6.197 ↑ 9.9 221 1

Hash Join (cost=137.15..10,738.94 rows=2,193 width=82) (actual time=0.801..6.197 rows=221 loops=1)

  • Hash Cond: (pro.pro_cd_produto_grupo = pgr.pgr_cd_produto_grupo)
16. 0.168 5.418 ↑ 9.9 221 1

Nested Loop (cost=1.84..10,597.87 rows=2,193 width=42) (actual time=0.066..5.418 rows=221 loops=1)

17. 0.659 5.029 ↑ 9.9 221 1

Nested Loop (cost=1.42..9,609.65 rows=2,193 width=42) (actual time=0.054..5.029 rows=221 loops=1)

18. 0.285 2.709 ↑ 1.3 1,661 1

Nested Loop (cost=1.13..8,911.40 rows=2,202 width=46) (actual time=0.044..2.709 rows=1,661 loops=1)

19. 0.052 0.630 ↑ 2.2 598 1

Nested Loop (cost=0.70..1,566.52 rows=1,339 width=22) (actual time=0.031..0.630 rows=598 loops=1)

20. 0.008 0.008 ↑ 1.0 1 1

Index Scan using unidade_negocio_pkey on unidade_negocio unn_1 (cost=0.28..2.49 rows=1 width=22) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
21. 0.570 0.570 ↑ 2.2 598 1

Index Scan using nota_saida_unidade_negocio_emissao_status on nota_saida nts (cost=0.42..1,550.63 rows=1,339 width=8) (actual time=0.021..0.570 rows=598 loops=1)

  • Index Cond: ((nts_cd_unidade_negocio = 180) AND (nts_dt_emissao >= '2019-11-01'::date) AND (nts_dt_emissao <= '2019-12-01'::date) AND (nts_fl_status = 'A'::bpchar))
  • Filter: ((nts_ds_nfe_chave IS NOT NULL) AND (nts_fl_tipo = 'S'::bpchar))
22. 1.794 1.794 ↑ 1.3 3 598

Index Scan using nota_saida_item_idx_nota_saida on nota_saida_item nsi (cost=0.43..5.45 rows=4 width=32) (actual time=0.002..0.003 rows=3 loops=598)

  • Index Cond: (nsi_cd_nota_saida = nts.nts_cd_nota_saida)
23. 1.661 1.661 ↓ 0.0 0 1,661

Index Scan using cfop_pkey on cfop cfo (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,661)

  • Index Cond: (cfo_cd_cfop = nsi.nsi_cd_cfop)
  • Filter: (((cfo_ds_cfop)::text <> '5.929'::text) AND ((cfo_ds_cfop)::text <> '6.929'::text))
  • Rows Removed by Filter: 1
24. 0.221 0.221 ↑ 1.0 1 221

Index Scan using produto_pkey on produto pro (cost=0.42..0.45 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=221)

  • Index Cond: (pro_cd_produto = nsi.nsi_cd_produto)
25. 0.380 0.720 ↑ 1.0 3,569 1

Hash (cost=90.69..90.69 rows=3,569 width=16) (actual time=0.720..0.720 rows=3,569 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 207kB
26. 0.340 0.340 ↑ 1.0 3,569 1

Seq Scan on produto_grupo pgr (cost=0.00..90.69 rows=3,569 width=16) (actual time=0.012..0.340 rows=3,569 loops=1)

27. 0.000 0.034 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=8.61..8.67 rows=1 width=250) (actual time=0.034..0.034 rows=0 loops=1)

28. 0.001 0.034 ↓ 0.0 0 1

GroupAggregate (cost=8.61..8.66 rows=1 width=210) (actual time=0.034..0.034 rows=0 loops=1)

  • Group Key: nmi.nmi_ds_cst_pis, nmi.nmi_vl_pr_pis, nmi.nmi_vl_pr_cofins, pgr_1.pgr_ds_produto_grupo, 'S'::text, unn_2.unn_ds_fantasia
29. 0.009 0.033 ↓ 0.0 0 1

Sort (cost=8.61..8.61 rows=1 width=134) (actual time=0.033..0.033 rows=0 loops=1)

  • Sort Key: nmi.nmi_ds_cst_pis, nmi.nmi_vl_pr_pis, nmi.nmi_vl_pr_cofins, pgr_1.pgr_ds_produto_grupo, unn_2.unn_ds_fantasia
  • Sort Method: quicksort Memory: 25kB
30. 0.000 0.024 ↓ 0.0 0 1

Nested Loop (cost=2.65..8.60 rows=1 width=134) (actual time=0.024..0.024 rows=0 loops=1)

31. 0.001 0.024 ↓ 0.0 0 1

Nested Loop (cost=2.38..6.09 rows=1 width=92) (actual time=0.023..0.024 rows=0 loops=1)

32. 0.000 0.023 ↓ 0.0 0 1

Nested Loop (cost=2.10..5.80 rows=1 width=84) (actual time=0.023..0.023 rows=0 loops=1)

33. 0.007 0.023 ↓ 0.0 0 1

Hash Join (cost=1.68..3.16 rows=1 width=80) (actual time=0.023..0.023 rows=0 loops=1)

  • Hash Cond: (nmi.nmi_cd_nota_manual = ntm.ntm_cd_nota_manual)
34. 0.009 0.009 ↑ 37.0 1 1

Seq Scan on nota_manual_item nmi (cost=0.00..1.37 rows=37 width=80) (actual time=0.008..0.009 rows=1 loops=1)

35. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=1.67..1.67 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
36. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on nota_manual ntm (cost=0.00..1.67 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: ((ntm_dt_emissao >= '2019-11-01'::date) AND (ntm_dt_emissao <= '2019-12-01'::date) AND (ntm_cd_unidade_negocio = 180))
  • Rows Removed by Filter: 38
37. 0.000 0.000 ↓ 0.0 0

Index Scan using produto_pkey on produto pro_1 (cost=0.42..2.64 rows=1 width=8) (never executed)

  • Index Cond: (pro_cd_produto = nmi.nmi_cd_produto)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using produto_grupo_pkey on produto_grupo pgr_1 (cost=0.28..0.30 rows=1 width=16) (never executed)

  • Index Cond: (pgr_cd_produto_grupo = pro_1.pro_cd_produto_grupo)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using unidade_negocio_pkey on unidade_negocio unn_2 (cost=0.28..2.49 rows=1 width=22) (never executed)

  • Index Cond: (unn_cd_unidade_negocio = 180)
40. 0.001 0.940 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=524.85..527.95 rows=59 width=216) (actual time=0.940..0.940 rows=0 loops=1)

41. 0.001 0.939 ↓ 0.0 0 1

GroupAggregate (cost=524.85..527.36 rows=59 width=166) (actual time=0.939..0.939 rows=0 loops=1)

  • Group Key: nsi_1.nsi_ds_cst_pis, nsi_1.nsi_vl_pr_pis, nsi_1.nsi_vl_pr_cofins, pgr_2.pgr_ds_produto_grupo, 'E'::text, unn_3.unn_ds_fantasia
42. 0.005 0.938 ↓ 0.0 0 1

Sort (cost=524.85..525.00 rows=59 width=82) (actual time=0.938..0.938 rows=0 loops=1)

  • Sort Key: nsi_1.nsi_ds_cst_pis, nsi_1.nsi_vl_pr_pis, nsi_1.nsi_vl_pr_cofins, pgr_2.pgr_ds_produto_grupo, unn_3.unn_ds_fantasia
  • Sort Method: quicksort Memory: 25kB
43. 0.000 0.933 ↓ 0.0 0 1

Nested Loop (cost=200.36..523.12 rows=59 width=82) (actual time=0.933..0.933 rows=0 loops=1)

44. 0.000 0.933 ↓ 0.0 0 1

Nested Loop (cost=200.08..505.55 rows=59 width=42) (actual time=0.933..0.933 rows=0 loops=1)

45. 0.000 0.933 ↓ 0.0 0 1

Nested Loop (cost=199.66..478.96 rows=59 width=42) (actual time=0.933..0.933 rows=0 loops=1)

46. 0.001 0.933 ↓ 0.0 0 1

Nested Loop (cost=199.36..460.25 rows=59 width=46) (actual time=0.932..0.933 rows=0 loops=1)

47. 0.006 0.932 ↓ 0.0 0 1

Nested Loop (cost=198.94..242.91 rows=36 width=22) (actual time=0.932..0.932 rows=0 loops=1)

48. 0.008 0.008 ↑ 1.0 1 1

Index Scan using unidade_negocio_pkey on unidade_negocio unn_3 (cost=0.28..2.49 rows=1 width=22) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
49. 0.001 0.918 ↓ 0.0 0 1

Bitmap Heap Scan on nota_saida nts_1 (cost=198.66..240.05 rows=36 width=8) (actual time=0.918..0.918 rows=0 loops=1)

  • Recheck Cond: ((nts_cd_unidade_negocio = 180) AND (nts_dt_emissao >= '2019-11-01'::date) AND (nts_dt_emissao <= '2019-12-01'::date) AND (nts_fl_tipo = 'E'::bpchar))
  • Filter: (nts_fl_status = 'A'::bpchar)
50. 0.024 0.917 ↓ 0.0 0 1

BitmapAnd (cost=198.66..198.66 rows=37 width=0) (actual time=0.917..0.917 rows=0 loops=1)

51. 0.054 0.054 ↑ 2.4 622 1

Bitmap Index Scan on nota_saida_unidade_negocio_data_emissao (cost=0.00..24.26 rows=1,467 width=0) (actual time=0.054..0.054 rows=622 loops=1)

  • Index Cond: ((nts_cd_unidade_negocio = 180) AND (nts_dt_emissao >= '2019-11-01'::date) AND (nts_dt_emissao <= '2019-12-01'::date))
52. 0.839 0.839 ↑ 1.0 15,708 1

Bitmap Index Scan on nota_saida_idx_tipo (cost=0.00..174.13 rows=16,414 width=0) (actual time=0.839..0.839 rows=15,708 loops=1)

  • Index Cond: (nts_fl_tipo = 'E'::bpchar)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using nota_saida_item_idx_nota_saida on nota_saida_item nsi_1 (cost=0.43..6.00 rows=4 width=32) (never executed)

  • Index Cond: (nsi_cd_nota_saida = nts_1.nts_cd_nota_saida)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using cfop_pkey on cfop cfo_1 (cost=0.29..0.32 rows=1 width=4) (never executed)

  • Index Cond: (cfo_cd_cfop = nsi_1.nsi_cd_cfop)
  • Filter: (((cfo_ds_cfop)::text <> '5.929'::text) AND ((cfo_ds_cfop)::text <> '6.929'::text))
55. 0.000 0.000 ↓ 0.0 0

Index Scan using produto_pkey on produto pro_2 (cost=0.42..0.45 rows=1 width=8) (never executed)

  • Index Cond: (pro_cd_produto = nsi_1.nsi_cd_produto)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using produto_grupo_pkey on produto_grupo pgr_2 (cost=0.28..0.30 rows=1 width=16) (never executed)

  • Index Cond: (pgr_cd_produto_grupo = pro_2.pro_cd_produto_grupo)
57. 0.003 2.612 ↑ 88.0 13 1

Subquery Scan on *SELECT* 5 (cost=5,638.07..5,669.53 rows=1,144 width=216) (actual time=2.597..2.612 rows=13 loops=1)

58. 0.275 2.609 ↑ 88.0 13 1

HashAggregate (cost=5,638.07..5,658.09 rows=1,144 width=166) (actual time=2.596..2.609 rows=13 loops=1)

  • Group Key: nei.nei_ds_cst_pis, nei.nei_vl_pr_pis, nei.nei_vl_pr_cofins, pgr_3.pgr_ds_produto_grupo, 'E'::text, unn_4.unn_ds_fantasia
59. 0.069 2.334 ↑ 3.4 338 1

Hash Join (cost=136.85..5,612.33 rows=1,144 width=82) (actual time=0.769..2.334 rows=338 loops=1)

  • Hash Cond: (pro_3.pro_cd_produto_grupo = pgr_3.pgr_cd_produto_grupo)
60. 0.096 1.548 ↑ 3.4 338 1

Nested Loop (cost=1.54..5,474.03 rows=1,144 width=42) (actual time=0.041..1.548 rows=338 loops=1)

61. 0.095 0.776 ↑ 3.4 338 1

Nested Loop (cost=1.12..4,956.46 rows=1,144 width=42) (actual time=0.032..0.776 rows=338 loops=1)

62. 0.015 0.153 ↑ 2.4 176 1

Nested Loop (cost=0.70..453.84 rows=420 width=22) (actual time=0.019..0.153 rows=176 loops=1)

63. 0.004 0.004 ↑ 1.0 1 1

Index Scan using unidade_negocio_pkey on unidade_negocio unn_4 (cost=0.28..2.49 rows=1 width=22) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
64. 0.134 0.134 ↑ 2.4 176 1

Index Scan using nota_entrada_unidade_negocio_entrada_cancelada on nota_entrada nte (cost=0.42..447.15 rows=420 width=8) (actual time=0.014..0.134 rows=176 loops=1)

  • Index Cond: ((nte_cd_unidade_negocio = 180) AND (nte_dt_entrada >= '2019-11-01'::date) AND (nte_dt_entrada <= '2019-12-01'::date))
65. 0.528 0.528 ↑ 4.5 2 176

Index Scan using nota_entrada_item_idx_nota_entrada on nota_entrada_item nei (cost=0.42..10.63 rows=9 width=28) (actual time=0.003..0.003 rows=2 loops=176)

  • Index Cond: (nei_cd_nota_entrada = nte.nte_cd_nota_entrada)
66. 0.676 0.676 ↑ 1.0 1 338

Index Scan using produto_pkey on produto pro_3 (cost=0.42..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=338)

  • Index Cond: (pro_cd_produto = nei.nei_cd_produto)
67. 0.386 0.717 ↑ 1.0 3,569 1

Hash (cost=90.69..90.69 rows=3,569 width=16) (actual time=0.717..0.717 rows=3,569 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 207kB
68. 0.331 0.331 ↑ 1.0 3,569 1

Seq Scan on produto_grupo pgr_3 (cost=0.00..90.69 rows=3,569 width=16) (actual time=0.008..0.331 rows=3,569 loops=1)

Execution time : 2,844.503 ms