explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bXht

Settings
# exclusive inclusive rows x rows loops node
1. 3.191 12,342.316 ↑ 1.0 1 1

Aggregate (cost=2,418,034.25..2,418,034.26 rows=1 width=32) (actual time=12,342.314..12,342.316 rows=1 loops=1)

2. 23.029 12,303.304 ↑ 91.6 317 1

Hash Left Join (cost=63,301.88..1,991,174.75 rows=29,043 width=12) (actual time=1,942.231..12,303.304 rows=317 loops=1)

  • Hash Cond: (substr((d.classificacao)::text, 1, 3) = (dsgl.linha)::text)
  • Filter: ((tb.posicaomes - (CASE WHEN ((((SubPlan 5))::date >= ((SubPlan 6))::date) AND (COALESCE((SubPlan 7), '0'::numeric) = '0'::numeric)) THEN (SubPlan 8) ELSE COALESCE((SubPlan 9), (SubPlan 10)) END)::date) > COALESCE(dsgsl.dias, dsgl.dias))
  • Rows Removed by Filter: 770
3. 3.564 12,065.221 ↑ 80.2 1,087 1

Hash Left Join (cost=63,300.43..1,955,866.43 rows=87,128 width=59) (actual time=1,931.321..12,065.221 rows=1,087 loops=1)

  • Hash Cond: (substr((d.classificacao)::text, 1, 5) = (dsgsl.linha)::text)
4. 3.822 12,061.566 ↑ 80.2 1,087 1

Hash Left Join (cost=63,298.98..1,955,620.04 rows=87,128 width=55) (actual time=1,931.143..12,061.566 rows=1,087 loops=1)

  • Hash Cond: ((p.iddepartamento)::integer = (d.iddepartamento)::integer)
5. 8.122 12,046.016 ↑ 80.2 1,087 1

Hash Left Join (cost=63,130.07..1,955,222.21 rows=87,128 width=52) (actual time=1,919.320..12,046.016 rows=1,087 loops=1)

  • Hash Cond: ((tb.idproduto)::integer = (p.idproduto)::integer)
6. 489.020 11,556.696 ↑ 80.2 1,087 1

Subquery Scan on tb (cost=53,412.42..1,945,275.85 rows=87,128 width=48) (actual time=1,435.038..11,556.696 rows=1,087 loops=1)

  • Filter: ((split_part(COALESCE((SubPlan 4), '0;0'::text), ';'::text, 1))::numeric(14,2) <> '0'::numeric)
  • Rows Removed by Filter: 85,310
7. 724.735 5,365.474 ↑ 1.0 86,397 1

HashAggregate (cost=53,412.42..657,398.91 rows=87,566 width=54) (actual time=1,417.761..5,365.474 rows=86,397 loops=1)

  • Group Key: pg.idfilial, pg.idproduto, pg.idgradex, pg.idgradey, pd.idsituacaoproduto
8. 433.443 1,271.256 ↑ 1.0 86,397 1

Hash Join (cost=42,516.08..52,317.85 rows=87,566 width=14) (actual time=487.698..1,271.256 rows=86,397 loops=1)

  • Hash Cond: (((pd.idproduto)::integer = (pg.idproduto)::integer) AND ((pd.idgradex)::smallint = (pg.idgradex)::smallint) AND ((pd.idgradey)::smallint = (pg.idgradey)::smallint))
9. 351.086 351.086 ↑ 1.0 256,352 1

Seq Scan on produtograde pd (cost=0.00..7,782.84 rows=256,352 width=10) (actual time=0.029..351.086 rows=256,352 loops=1)

  • Filter: ((idsituacaoproduto)::smallint = ANY ('{1,2,3}'::integer[]))
10. 118.968 486.727 ↑ 1.0 86,397 1

Hash (cost=40,983.68..40,983.68 rows=87,566 width=12) (actual time=486.726..486.727 rows=86,397 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,737kB
11. 367.759 367.759 ↑ 1.0 86,397 1

Index Only Scan using pk_produtogradefilial on produtogradefilial pg (cost=0.56..40,983.68 rows=87,566 width=12) (actual time=0.162..367.759 rows=86,397 loops=1)

  • Index Cond: (idfilial = 10,002)
  • Heap Fetches: 13,391
12.          

SubPlan (for HashAggregate)

13. 1,382.352 3,369.483 ↓ 0.0 0 86,397

Limit (cost=0.82..6.88 rows=1 width=52) (actual time=0.039..0.039 rows=0 loops=86,397)

14. 1,987.131 1,987.131 ↓ 0.0 0 86,397

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_8 (cost=0.82..6.88 rows=1 width=52) (actual time=0.023..0.023 rows=0 loops=86,397)

  • Index Cond: (((idfilial)::integer = 10,002) AND ((idproduto)::integer = (pg.idproduto)::integer) AND ((idgradex)::smallint = (pg.idgradex)::smallint) AND ((idgradey)::smallint = (pg.idgradey)::smallint) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)))
  • Filter: ((idoperacaoproduto IS NOT NULL) AND (saldo = '0'::numeric) AND ((idoperacaoproduto)::integer = 102,010))
  • Rows Removed by Filter: 0
15.          

SubPlan (for Subquery Scan)

16. 518.382 5,702.202 ↑ 1.0 1 86,397

Subquery Scan on ii_1 (cost=14.65..14.69 rows=1 width=32) (actual time=0.063..0.066 rows=1 loops=86,397)

17. 604.779 5,183.820 ↑ 1.0 1 86,397

Aggregate (cost=14.65..14.66 rows=1 width=64) (actual time=0.059..0.060 rows=1 loops=86,397)

18.          

Initplan (for Aggregate)

19. 1,468.749 2,332.719 ↓ 0.0 0 86,397

Limit (cost=0.82..6.86 rows=1 width=56) (actual time=0.027..0.027 rows=0 loops=86,397)

20. 863.970 863.970 ↓ 0.0 0 86,397

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_1 (cost=0.82..6.86 rows=1 width=56) (actual time=0.009..0.010 rows=0 loops=86,397)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)))
  • Filter: ((idoperacaoproduto)::integer > 0)
  • Rows Removed by Filter: 0
21. 386.652 2,246.322 ↓ 0.0 0 86,397

Nested Loop (cost=0.56..7.78 rows=1 width=5) (actual time=0.021..0.026 rows=0 loops=86,397)

22. 1,727.940 1,727.940 ↓ 0.0 0 86,397

Index Scan using idx_produtogradesaldofilial_filialprodutograde on produtogradesaldofilial pgsf_1 (cost=0.56..6.59 rows=1 width=5) (actual time=0.017..0.020 rows=0 loops=86,397)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint))
  • Filter: ((idtiposaldoproduto)::smallint = 1)
  • Rows Removed by Filter: 1
23. 131.730 131.730 ↑ 1.0 1 26,346

Seq Scan on tiposaldoproduto tp_1 (cost=0.00..1.18 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=26,346)

  • Filter: ((idtiposaldoproduto)::smallint = 1)
  • Rows Removed by Filter: 13
24. 221.012 481.198 ↑ 1.0 181,940 1

Hash (cost=7,443.40..7,443.40 rows=181,940 width=12) (actual time=481.197..481.198 rows=181,940 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,848kB
25. 260.186 260.186 ↑ 1.0 181,940 1

Seq Scan on produto p (cost=0.00..7,443.40 rows=181,940 width=12) (actual time=0.024..260.186 rows=181,940 loops=1)

26. 5.487 11.728 ↑ 1.0 4,796 1

Hash (cost=108.96..108.96 rows=4,796 width=11) (actual time=11.728..11.728 rows=4,796 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 270kB
27. 6.241 6.241 ↑ 1.0 4,796 1

Seq Scan on departamento d (cost=0.00..108.96 rows=4,796 width=11) (actual time=0.020..6.241 rows=4,796 loops=1)

28. 0.026 0.091 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.091..0.091 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.065 0.065 ↑ 1.0 20 1

Seq Scan on compras_departamento_dias_sem_giro dsgsl (cost=0.00..1.20 rows=20 width=8) (actual time=0.048..0.065 rows=20 loops=1)

30. 0.027 0.053 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.052..0.053 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.026 0.026 ↑ 1.0 20 1

Seq Scan on compras_departamento_dias_sem_giro dsgl (cost=0.00..1.20 rows=20 width=8) (actual time=0.008..0.026 rows=20 loops=1)

32.          

SubPlan (for Hash Left Join)

33. 27.175 46.741 ↑ 1.0 1 1,087

Limit (cost=0.84..6.89 rows=1 width=20) (actual time=0.041..0.043 rows=1 loops=1,087)

34. 19.566 19.566 ↑ 1.0 1 1,087

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_2 (cost=0.84..6.89 rows=1 width=20) (actual time=0.017..0.018 rows=1 loops=1,087)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date >= (split_part(tb.ultimosaldozerado, ';'::text, 1))::date) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)) AND ((idmovimento)::integer > (split_part(tb.ultimosaldozerado, ';'::text, 2))::integer))
  • Filter: ((saldo > '0'::numeric) AND ((idregistronota)::integer > 0) AND ((idoperacaoproduto)::integer = ANY ('{101030,101010,101025}'::integer[])) AND (COALESCE((iddescarga)::integer, 0) <> ALL ('{1119,0}'::integer[])))
  • Rows Removed by Filter: 2
35. 10.870 68.481 ↑ 1.0 1 1,087

Limit (cost=1.83..19.91 rows=1 width=20) (actual time=0.055..0.063 rows=1 loops=1,087)

36. 23.684 57.611 ↑ 1.0 1 1,087

Nested Loop Anti Join (cost=1.83..19.91 rows=1 width=20) (actual time=0.053..0.053 rows=1 loops=1,087)

37. 16.305 16.305 ↑ 1.0 1 1,087

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_3 (cost=0.83..6.87 rows=1 width=29) (actual time=0.013..0.015 rows=1 loops=1,087)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date >= (split_part(tb.ultimosaldozerado, ';'::text, 1))::date) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)))
  • Filter: ((idoperacaoproduto)::integer = ANY ('{102010,102020}'::integer[]))
  • Rows Removed by Filter: 1
38. 2.641 17.622 ↓ 0.0 0 979

Nested Loop (cost=1.00..13.04 rows=1 width=13) (actual time=0.017..0.018 rows=0 loops=979)

  • Join Filter: (ibd.quantidade = ib_3.quantidade)
39. 11.748 11.748 ↓ 0.0 0 979

Index Scan using ix_fk_itemnotadevolucao_itembasereferente on itemnotadevolucao ind (cost=0.43..6.45 rows=1 width=12) (actual time=0.011..0.012 rows=0 loops=979)

  • Index Cond: (((idfilial)::integer = (ib_3.idfilial)::integer) AND ((idfilial)::integer = (tb.idfilial)::integer) AND ((iditembasereferente)::integer = (ib_3.iditembase)::integer))
40. 3.233 3.233 ↑ 1.0 1 61

Index Scan using pk_itembase on itembase ibd (cost=0.57..6.58 rows=1 width=13) (actual time=0.041..0.053 rows=1 loops=61)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((iditembase)::integer = (ind.iditembasedevolucao)::integer))
41. 3.332 34.986 ↓ 0.0 0 833

Limit (cost=1.84..19.92 rows=1 width=24) (actual time=0.041..0.042 rows=0 loops=833)

42. 19.133 31.654 ↓ 0.0 0 833

Nested Loop Anti Join (cost=1.84..19.92 rows=1 width=24) (actual time=0.038..0.038 rows=0 loops=833)

43. 10.829 10.829 ↓ 0.0 0 833

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_4 (cost=0.84..6.88 rows=1 width=33) (actual time=0.013..0.013 rows=0 loops=833)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date >= (split_part(tb.ultimosaldozerado, ';'::text, 1))::date) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)) AND ((idmovimento)::integer > (split_part(tb.ultimosaldozerado, ';'::text, 2))::integer))
  • Filter: ((idoperacaoproduto)::integer = ANY ('{102010,102020}'::integer[]))
  • Rows Removed by Filter: 2
44. 0.608 1.692 ↓ 0.0 0 141

Nested Loop (cost=1.00..13.04 rows=1 width=13) (actual time=0.011..0.012 rows=0 loops=141)

  • Join Filter: (ibd_1.quantidade = ib_4.quantidade)
45. 0.846 0.846 ↓ 0.0 0 141

Index Scan using ix_fk_itemnotadevolucao_itembasereferente on itemnotadevolucao ind_1 (cost=0.43..6.45 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=141)

  • Index Cond: (((idfilial)::integer = (ib_4.idfilial)::integer) AND ((idfilial)::integer = (tb.idfilial)::integer) AND ((iditembasereferente)::integer = (ib_4.iditembase)::integer))
46. 0.238 0.238 ↑ 1.0 1 17

Index Scan using pk_itembase on itembase ibd_1 (cost=0.57..6.58 rows=1 width=13) (actual time=0.008..0.014 rows=1 loops=17)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((iditembase)::integer = (ind_1.iditembasedevolucao)::integer))
47. 8.289 13.508 ↑ 1.0 1 307

Limit (cost=0.84..6.89 rows=1 width=20) (actual time=0.042..0.044 rows=1 loops=307)

48. 5.219 5.219 ↑ 1.0 1 307

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_5 (cost=0.84..6.89 rows=1 width=20) (actual time=0.016..0.017 rows=1 loops=307)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date >= (split_part(tb.ultimosaldozerado, ';'::text, 1))::date) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)) AND ((idmovimento)::integer > (split_part(tb.ultimosaldozerado, ';'::text, 2))::integer))
  • Filter: ((saldo > '0'::numeric) AND ((idregistronota)::integer > 0) AND ((idoperacaoproduto)::integer = ANY ('{101030,101010,101025}'::integer[])) AND (COALESCE((iddescarga)::integer, 0) <> ALL ('{1119,0}'::integer[])))
  • Rows Removed by Filter: 1
49. 7.800 43.680 ↑ 1.0 1 780

Limit (cost=1.83..19.91 rows=1 width=20) (actual time=0.048..0.056 rows=1 loops=780)

50. 17.601 35.880 ↑ 1.0 1 780

Nested Loop Anti Join (cost=1.83..19.91 rows=1 width=20) (actual time=0.046..0.046 rows=1 loops=780)

51. 10.920 10.920 ↑ 1.0 1 780

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_6 (cost=0.83..6.87 rows=1 width=29) (actual time=0.013..0.014 rows=1 loops=780)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date >= (split_part(tb.ultimosaldozerado, ';'::text, 1))::date) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)))
  • Filter: ((idoperacaoproduto)::integer = ANY ('{102010,102020}'::integer[]))
  • Rows Removed by Filter: 1
52. 2.359 7.359 ↓ 0.0 0 669

Nested Loop (cost=1.00..13.04 rows=1 width=13) (actual time=0.010..0.011 rows=0 loops=669)

  • Join Filter: (ibd_2.quantidade = ib_6.quantidade)
53. 4.014 4.014 ↓ 0.0 0 669

Index Scan using ix_fk_itemnotadevolucao_itembasereferente on itemnotadevolucao ind_2 (cost=0.43..6.45 rows=1 width=12) (actual time=0.005..0.006 rows=0 loops=669)

  • Index Cond: (((idfilial)::integer = (ib_6.idfilial)::integer) AND ((idfilial)::integer = (tb.idfilial)::integer) AND ((iditembasereferente)::integer = (ib_6.iditembase)::integer))
54. 0.986 0.986 ↑ 1.0 1 58

Index Scan using pk_itembase on itembase ibd_2 (cost=0.57..6.58 rows=1 width=13) (actual time=0.009..0.017 rows=1 loops=58)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((iditembase)::integer = (ind_2.iditembasedevolucao)::integer))
55. 5.070 7.605 ↑ 1.0 1 169

Limit (cost=0.84..6.89 rows=1 width=20) (actual time=0.043..0.045 rows=1 loops=169)

56. 2.535 2.535 ↑ 1.0 1 169

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib_7 (cost=0.84..6.89 rows=1 width=20) (actual time=0.015..0.015 rows=1 loops=169)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date >= (split_part(tb.ultimosaldozerado, ';'::text, 1))::date) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)) AND ((idmovimento)::integer > (split_part(tb.ultimosaldozerado, ';'::text, 2))::integer))
  • Filter: ((saldo > '0'::numeric) AND ((idregistronota)::integer > 0) AND ((idoperacaoproduto)::integer = ANY ('{101030,101010,101025}'::integer[])) AND (COALESCE((iddescarga)::integer, 0) <> ALL ('{1119,0}'::integer[])))
  • Rows Removed by Filter: 1
57.          

SubPlan (for Aggregate)

58. 3.170 35.821 ↑ 1.0 1 317

Subquery Scan on ii (cost=14.65..14.69 rows=1 width=32) (actual time=0.110..0.113 rows=1 loops=317)

59. 5.072 32.651 ↑ 1.0 1 317

Aggregate (cost=14.65..14.66 rows=1 width=64) (actual time=0.102..0.103 rows=1 loops=317)

60.          

Initplan (for Aggregate)

61. 8.559 14.582 ↑ 1.0 1 317

Limit (cost=0.82..6.86 rows=1 width=56) (actual time=0.043..0.046 rows=1 loops=317)

62. 6.023 6.023 ↑ 1.0 1 317

Index Scan using idx_itembase_saldoanteriorposicao_desc on itembase ib (cost=0.82..6.86 rows=1 width=56) (actual time=0.018..0.019 rows=1 loops=317)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint) AND ((datamovimento)::date <= gazin.ultimo_dia_mes(('20190901'::cstring)::date)))
  • Filter: ((idoperacaoproduto)::integer > 0)
  • Rows Removed by Filter: 0
63. 4.445 12.997 ↓ 2.0 2 317

Nested Loop (cost=0.56..7.78 rows=1 width=5) (actual time=0.021..0.041 rows=2 loops=317)

64. 5.072 5.072 ↓ 2.0 2 317

Index Scan using idx_produtogradesaldofilial_filialprodutograde on produtogradesaldofilial pgsf (cost=0.56..6.59 rows=1 width=5) (actual time=0.010..0.016 rows=2 loops=317)

  • Index Cond: (((idfilial)::integer = (tb.idfilial)::integer) AND ((idproduto)::integer = (tb.idproduto)::integer) AND ((idgradex)::smallint = (tb.idgradex)::smallint) AND ((idgradey)::smallint = (tb.idgradey)::smallint))
  • Filter: ((idtiposaldoproduto)::smallint = 1)
  • Rows Removed by Filter: 2
65. 3.480 3.480 ↑ 1.0 1 696

Seq Scan on tiposaldoproduto tp (cost=0.00..1.18 rows=1 width=4) (actual time=0.002..0.005 rows=1 loops=696)

  • Filter: ((idtiposaldoproduto)::smallint = 1)
  • Rows Removed by Filter: 13