explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HXS2

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 2,864.591 ↑ 11.3 31 1

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

  • Functions: 183
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 20.482 ms, Inlining 20.304 ms, Optimization 1465.232 ms, Emission 1029.402 ms, Total 2535.420 ms
2. 0.063 2,864.548 ↑ 11.3 31 1

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

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

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

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

5. 0.005 2,864.416 ↑ 97.1 36 1

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

6. 0.004 2,854.441 ↑ 5.9 17 1

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

7. 46.725 2,854.437 ↑ 5.9 17 1

HashAggregate (cost=791,617.85..791,621.60 rows=100 width=274) (actual time=2,854.426..2,854.437 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. 37.612 2,807.712 ↑ 210.3 39,092 1

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

9. 2,521.875 2,540.710 ↑ 2.5 32,770 1

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

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

  • Index Cond: (unn_cd_unidade_negocio = 180)
11. 18.822 18.822 ↑ 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.014..18.822 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.003 6.400 ↑ 365.5 6 1

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

14. 0.208 6.397 ↑ 365.5 6 1

HashAggregate (cost=10,788.28..10,826.66 rows=2,193 width=166) (actual time=6.383..6.397 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.057 6.189 ↑ 9.9 221 1

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

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

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

17. 0.647 5.023 ↑ 9.9 221 1

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

18. 0.280 2.715 ↑ 1.3 1,661 1

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

19. 0.051 0.641 ↑ 2.2 598 1

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

20. 0.007 0.007 ↑ 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.006..0.007 rows=1 loops=1)

  • Index Cond: (unn_cd_unidade_negocio = 180)
21. 0.583 0.583 ↑ 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.022..0.583 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.379 0.721 ↑ 1.0 3,569 1

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

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

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

27. 0.001 0.039 ↓ 0.0 0 1

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

28. 0.000 0.038 ↓ 0.0 0 1

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

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

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

31. 0.001 0.029 ↓ 0.0 0 1

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

32. 0.000 0.028 ↓ 0.0 0 1

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

33. 0.008 0.028 ↓ 0.0 0 1

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

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

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

35. 0.001 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: 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 0.923 ↓ 0.0 0 1

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

41. 0.001 0.923 ↓ 0.0 0 1

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

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

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

44. 0.001 0.918 ↓ 0.0 0 1

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

45. 0.000 0.917 ↓ 0.0 0 1

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

46. 0.000 0.917 ↓ 0.0 0 1

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

47. 0.005 0.917 ↓ 0.0 0 1

Nested Loop (cost=198.94..242.91 rows=36 width=22) (actual time=0.916..0.917 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.006..0.006 rows=1 loops=1)

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

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

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

51. 0.053 0.053 ↑ 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.053..0.053 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.823 0.823 ↑ 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.823..0.823 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.608 ↑ 88.0 13 1

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

58. 0.304 2.605 ↑ 88.0 13 1

HashAggregate (cost=5,638.07..5,658.09 rows=1,144 width=166) (actual time=2.592..2.605 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.074 2.301 ↑ 3.4 338 1

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

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

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

61. 0.087 0.772 ↑ 3.4 338 1

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

62. 0.015 0.157 ↑ 2.4 176 1

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

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

  • Index Cond: (unn_cd_unidade_negocio = 180)
64. 0.133 0.133 ↑ 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.133 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.393 0.713 ↑ 1.0 3,569 1

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

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

Planning time : 4.222 ms
Execution time : 2,885.761 ms