explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1DNm : Analise SQL 31/07/2020

Settings
# exclusive inclusive rows x rows loops node
1. 3.372 307,656.557 ↓ 1.7 816 1

Subquery Scan on geral (cost=24,729.42..24,836.56 rows=487 width=496) (actual time=307,651.289..307,656.557 rows=816 loops=1)

2. 1.871 307,653.185 ↓ 1.7 816 1

GroupAggregate (cost=24,729.42..24,773.25 rows=487 width=340) (actual time=307,651.250..307,653.185 rows=816 loops=1)

  • Group Key: prd.idproduto, dpto5.margemcontribuicao, dpto5.descricao, dpto4.descricao, dpto3.descricao, dpto2.descricao, dpto.descricao, pg.idgradex, pg.idgradey, prd.descricao, pg.idsituacaoproduto, gp.ultimaremarcacao, prd.idcodigonbm, (round((pgf (...)
3. 7.186 307,651.314 ↓ 3.4 1,632 1

Sort (cost=24,729.42..24,730.64 rows=487 width=319) (actual time=307,651.229..307,651.314 rows=1,632 loops=1)

  • Sort Key: prd.idproduto, dpto5.margemcontribuicao, dpto5.descricao, dpto4.descricao, dpto3.descricao, dpto2.descricao, dpto.descricao, pg.idgradex, pg.idgradey, prd.descricao, pg.idsituacaoproduto, gp.ultimaremarcacao, prd.idcodigonbm, (round (...)
  • Sort Method: quicksort Memory: 482kB
4. 7.186 307,644.128 ↓ 3.4 1,632 1

Nested Loop Left Join (cost=16.74..24,707.68 rows=487 width=319) (actual time=1,464.658..307,644.128 rows=1,632 loops=1)

  • Join Filter: (ldp.idsituacaoproduto = (pg.idsituacaoproduto)::smallint)
  • Rows Removed by Join Filter: 2,888
5. 33.785 307,635.310 ↓ 3.4 1,632 1

Merge Join (cost=16.74..24,679.85 rows=487 width=311) (actual time=1,464.634..307,635.310 rows=1,632 loops=1)

  • Merge Cond: ((prd.idmarca)::integer = (gm.idmarca)::integer)
6.          

Initplan (for Merge Join)

7. 0.034 0.034 ↑ 1.0 1 1

Index Scan using pk_filialparametro on filialparametro (cost=0.29..6.32 rows=1 width=32) (actual time=0.033..0.034 rows=1 loops=1)

  • Index Cond: (((idfilial)::integer = 10,001) AND ((idparametro)::integer = 71))
8. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_filialparametro on filialparametro filialparametro_1 (cost=0.29..6.32 rows=1 width=32) (never executed)

  • Index Cond: (((idfilial)::integer = 10,001) AND ((idparametro)::integer = 71))
9. 4.022 1,490.281 ↓ 3.4 1,632 1

Nested Loop (cost=3.82..11,085.13 rows=487 width=163) (actual time=0.932..1,490.281 rows=1,632 loops=1)

  • Join Filter: ((pg.idsituacaoproduto)::smallint = (sp.idsituacaoproduto)::smallint)
  • Rows Removed by Join Filter: 1,766
10. 6.871 1,484.627 ↓ 3.4 1,632 1

Nested Loop (cost=3.82..11,065.82 rows=487 width=163) (actual time=0.924..1,484.627 rows=1,632 loops=1)

11. 6.210 103.612 ↓ 3.0 1,632 1

Nested Loop (cost=3.38..8,531.14 rows=537 width=172) (actual time=0.771..103.612 rows=1,632 loops=1)

12. 8.225 89.242 ↓ 3.0 1,632 1

Nested Loop (cost=3.10..8,342.99 rows=546 width=151) (actual time=0.764..89.242 rows=1,632 loops=1)

13. 3.753 57.019 ↓ 1.8 1,846 1

Nested Loop (cost=2.68..7,755.60 rows=1,032 width=133) (actual time=0.717..57.019 rows=1,846 loops=1)

14. 3.885 39.634 ↓ 1.3 1,136 1

Nested Loop Left Join (cost=2.27..6,215.47 rows=876 width=123) (actual time=0.684..39.634 rows=1,136 loops=1)

15. 2.022 28.933 ↓ 1.3 1,136 1

Nested Loop Left Join (cost=1.98..5,950.43 rows=876 width=124) (actual time=0.666..28.933 rows=1,136 loops=1)

16. 0.894 21.231 ↓ 1.2 568 1

Nested Loop (cost=1.55..2,784.53 rows=488 width=128) (actual time=0.627..21.231 rows=568 loops=1)

17. 1.976 19.769 ↓ 1.2 568 1

Nested Loop (cost=1.40..2,772.26 rows=488 width=116) (actual time=0.603..19.769 rows=568 loops=1)

18. 1.686 17.225 ↓ 1.1 568 1

Nested Loop (cost=1.13..2,600.99 rows=497 width=101) (actual time=0.598..17.225 rows=568 loops=1)

19. 1.431 14.971 ↓ 1.1 568 1

Nested Loop (cost=0.85..2,426.62 rows=506 width=86) (actual time=0.592..14.971 rows=568 loops=1)

20. 2.133 12.404 ↓ 1.1 568 1

Nested Loop (cost=0.57..2,238.80 rows=515 width=71) (actual time=0.586..12.404 rows=568 loops=1)

21. 6.036 6.036 ↑ 1.2 605 1

Index Scan using ix_fk_produto_marca on produto prd (cost=0.29..1,946.60 rows=724 width=56) (actual time=0.577..6.036 rows=605 loops=1)

  • Index Cond: ((idmarca)::integer = ANY ('{936,1952,651,238,239,928,832,240,911,1034,63,211,241,476,1035,242,243,874,510,512,457,244,440,657,245,575,246,2 (...)
22. 4.235 4.235 ↑ 1.0 1 605

Index Scan using departamento_idx on departamento dpto (cost=0.28..0.40 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=605)

  • Index Cond: (((iddepartamento)::integer = (prd.iddepartamento)::integer) AND ((iddepartamento)::integer >= 1,010,000,000) AND ((iddepartamento)::integer <= (...)
23. 1.136 1.136 ↑ 1.0 1 568

Index Scan using departamento_idx on departamento dpto2 (cost=0.28..0.36 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=568)

  • Index Cond: ((iddepartamento)::integer = (dpto.idsubordinado)::integer)
24. 0.568 0.568 ↑ 1.0 1 568

Index Scan using departamento_idx on departamento dpto3 (cost=0.28..0.34 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=568)

  • Index Cond: ((iddepartamento)::integer = (dpto2.idsubordinado)::integer)
25. 0.568 0.568 ↑ 1.0 1 568

Index Scan using departamento_idx on departamento dpto4 (cost=0.28..0.34 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=568)

  • Index Cond: ((iddepartamento)::integer = (dpto3.idsubordinado)::integer)
26. 0.545 0.568 ↑ 1.0 1 568

Materialize (cost=0.14..6.17 rows=1 width=12) (actual time=0.000..0.001 rows=1 loops=568)

27. 0.023 0.023 ↑ 1.0 1 1

Index Scan using pk_filial on filial fil (cost=0.14..6.16 rows=1 width=12) (actual time=0.021..0.023 rows=1 loops=1)

  • Index Cond: ((idfilial)::integer = 10,001)
28. 5.680 5.680 ↑ 1.0 2 568

Index Only Scan using ix_end_01 on endereco edr (cost=0.43..6.47 rows=2 width=12) (actual time=0.007..0.010 rows=2 loops=568)

  • Index Cond: (idcnpj_cpf = (fil.idcnpj_cpf)::bigint)
  • Heap Fetches: 0
29. 6.816 6.816 ↑ 1.0 1 1,136

Index Scan using pk_cidade on cidade cid (cost=0.29..0.30 rows=1 width=7) (actual time=0.006..0.006 rows=1 loops=1,136)

  • Index Cond: ((idcidade)::integer = (edr.idcidade)::integer)
30. 13.632 13.632 ↓ 2.0 2 1,136

Index Only Scan using idx_produtograde01 on produtograde pg (cost=0.42..1.75 rows=1 width=10) (actual time=0.008..0.012 rows=2 loops=1,136)

  • Index Cond: (idproduto = (prd.idproduto)::integer)
  • Filter: ((idsituacaoproduto)::smallint = ANY ('{1,2,3}'::integer[]))
  • Heap Fetches: 182
31. 23.998 23.998 ↑ 1.0 1 1,846

Index Scan using ix_fk_produtogradeprecogrupo_produtograde on produtogradeprecogrupo gp (cost=0.42..0.57 rows=1 width=18) (actual time=0.013..0.013 rows=1 loops=1,846)

  • Index Cond: (((idproduto)::integer = (pg.idproduto)::integer) AND ((idgradex)::smallint = (pg.idgradex)::smallint) AND ((idgradey)::smallint = (pg.idgradey)::smallint))
  • Filter: ((idgrupopreco)::smallint = 6)
  • Rows Removed by Filter: 1
32. 8.160 8.160 ↑ 1.0 1 1,632

Index Scan using departamento_idx on departamento dpto5 (cost=0.28..0.34 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=1,632)

  • Index Cond: ((iddepartamento)::integer = (dpto4.idsubordinado)::integer)
33. 1,374.144 1,374.144 ↑ 1.0 1 1,632

Index Scan using ix_fk_produtogradefilial_produtograde on produtogradefilial pgf (cost=0.44..4.72 rows=1 width=15) (actual time=0.842..0.842 rows=1 loops=1,632)

  • Index Cond: (((idproduto)::integer = (pg.idproduto)::integer) AND ((idgradey)::smallint = (pg.idgradey)::smallint) AND ((idgradex)::smallint = (pg.idgradex)::smallint))
  • Filter: ((idfilial)::integer = 10,001)
  • Rows Removed by Filter: 129
34. 1.628 1.632 ↑ 1.5 2 1,632

Materialize (cost=0.00..1.04 rows=3 width=2) (actual time=0.000..0.001 rows=2 loops=1,632)

35. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on situacaoproduto sp (cost=0.00..1.03 rows=3 width=2) (actual time=0.003..0.004 rows=3 loops=1)

36. 0.234 0.234 ↑ 1.0 848 1

Index Scan using pk_marca on marca gm (cost=0.28..36.14 rows=875 width=12) (actual time=0.010..0.234 rows=848 loops=1)

37.          

SubPlan (for Merge Join)

38. 105,254.208 105,254.208 ↑ 1,000.0 1 1,632

Function Scan on ret_regrafiscal_itb (cost=0.26..10.26 rows=1,000 width=32) (actual time=64.494..64.494 rows=1 loops=1,632)

39. 99,775.584 99,775.584 ↑ 1,000.0 1 1,632

Function Scan on ret_regrafiscal_itb ret_regrafiscal_itb_1 (cost=0.26..10.26 rows=1,000 width=32) (actual time=61.137..61.137 rows=1 loops=1,632)

40. 101,081.184 101,081.184 ↑ 1,000.0 1 1,632

Function Scan on ret_regrafiscal_itb ret_regrafiscal_itb_2 (cost=0.26..10.26 rows=1,000 width=32) (actual time=61.936..61.937 rows=1 loops=1,632)

41. 1.615 1.632 ↑ 1.0 3 1,632

Materialize (cost=0.00..1.04 rows=3 width=8) (actual time=0.001..0.001 rows=3 loops=1,632)

42. 0.017 0.017 ↑ 1.0 3 1

Seq Scan on limitedescontoprodutos ldp (cost=0.00..1.03 rows=3 width=8) (actual time=0.017..0.017 rows=3 loops=1)

Planning time : 17.622 ms
Execution time : 307,657.824 ms