explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A3Se

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 2,816.499 ↑ 11.3 31 1

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

  • Functions: 183
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 19.941 ms, Inlining 18.166 ms, Optimization 1440.236 ms, Emission 998.150 ms, Total 2476.493 ms
2. 0.086 2,816.460 ↑ 11.3 31 1

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

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

Sort (cost=808,935.62..808,944.36 rows=3,497 width=394) (actual time=2,816.372..2,816.374 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.006 2,816.311 ↑ 97.1 36 1

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

5. 0.005 2,816.305 ↑ 97.1 36 1

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

6. 0.004 2,805.495 ↑ 5.9 17 1

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

7. 47.496 2,805.491 ↑ 5.9 17 1

HashAggregate (cost=791,617.85..791,621.60 rows=100 width=274) (actual time=2,805.480..2,805.491 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. 40.844 2,757.995 ↑ 210.3 39,092 1

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

9. 2,468.131 2,487.761 ↑ 2.5 32,770 1

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

10. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
11. 19.611 19.611 ↑ 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.015..19.611 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.991 ↑ 365.5 6 1

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

14. 0.227 6.989 ↑ 365.5 6 1

HashAggregate (cost=10,788.28..10,826.66 rows=2,193 width=166) (actual time=6.976..6.989 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.060 6.762 ↑ 9.9 221 1

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

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

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

17. 0.890 5.470 ↑ 9.9 221 1

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

18. 0.415 2.919 ↑ 1.3 1,661 1

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

19. 0.059 0.710 ↑ 2.2 598 1

Nested Loop (cost=0.70..1,566.52 rows=1,339 width=22) (actual time=0.030..0.710 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.643 0.643 ↑ 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.020..0.643 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.442 0.442 ↑ 1.0 1 221

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 207kB
26. 0.418 0.418 ↑ 1.0 3,569 1

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

27. 0.001 0.032 ↓ 0.0 0 1

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

28. 0.000 0.031 ↓ 0.0 0 1

GroupAggregate (cost=8.61..8.66 rows=1 width=210) (actual time=0.031..0.031 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.031 ↓ 0.0 0 1

Sort (cost=8.61..8.61 rows=1 width=134) (actual time=0.031..0.031 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.001 0.022 ↓ 0.0 0 1

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

31. 0.000 0.021 ↓ 0.0 0 1

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

32. 0.000 0.021 ↓ 0.0 0 1

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

33. 0.007 0.021 ↓ 0.0 0 1

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

  • Hash Cond: (nmi.nmi_cd_nota_manual = ntm.ntm_cd_nota_manual)
34. 0.008 0.008 ↑ 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.008 rows=1 loops=1)

35. 0.000 0.006 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
36. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on nota_manual ntm (cost=0.00..1.67 rows=1 width=8) (actual time=0.006..0.006 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.000 1.024 ↓ 0.0 0 1

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

41. 0.001 1.024 ↓ 0.0 0 1

GroupAggregate (cost=524.85..527.36 rows=59 width=166) (actual time=1.023..1.024 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 1.023 ↓ 0.0 0 1

Sort (cost=524.85..525.00 rows=59 width=82) (actual time=1.023..1.023 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.001 1.018 ↓ 0.0 0 1

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

44. 0.000 1.017 ↓ 0.0 0 1

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

45. 0.000 1.017 ↓ 0.0 0 1

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

46. 0.001 1.017 ↓ 0.0 0 1

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

47. 0.003 1.016 ↓ 0.0 0 1

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

48. 0.006 0.006 ↑ 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.005..0.006 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
49. 0.002 1.007 ↓ 0.0 0 1

Bitmap Heap Scan on nota_saida nts_1 (cost=198.66..240.05 rows=36 width=8) (actual time=1.007..1.007 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 1.005 ↓ 0.0 0 1

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

51. 0.072 0.072 ↑ 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.072..0.072 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.909 0.909 ↑ 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.909..0.909 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.758 ↑ 88.0 13 1

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

58. 0.304 2.755 ↑ 88.0 13 1

HashAggregate (cost=5,638.07..5,658.09 rows=1,144 width=166) (actual time=2.742..2.755 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.081 2.451 ↑ 3.4 338 1

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

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

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

61. 0.135 0.841 ↑ 3.4 338 1

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

62. 0.020 0.178 ↑ 2.4 176 1

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

63. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
64. 0.155 0.155 ↑ 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.026..0.155 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.404 0.745 ↑ 1.0 3,569 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 207kB
68. 0.341 0.341 ↑ 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.007..0.341 rows=3,569 loops=1)

Planning time : 5.286 ms
Execution time : 2,837.112 ms