explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7VAe : Optimization for: Optimization for: estoquedia; plan #eBBb; plan #tDmr

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 65,367.601 ↓ 0.0 0 1

GroupAggregate (cost=12,630,575.94..12,631,822.62 rows=5,667 width=208) (actual time=65,367.601..65,367.601 rows=0 loops=1)

  • Group Key: endfrom.idproduto, endfrom.dataentsai
  • Buffers: shared hit=145 read=448328
2. 0.011 65,367.599 ↓ 0.0 0 1

Sort (cost=12,630,575.94..12,630,717.61 rows=56,667 width=184) (actual time=65,367.599..65,367.599 rows=0 loops=1)

  • Sort Key: endfrom.idproduto, endfrom.dataentsai
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=145 read=448328
3. 0.001 65,367.588 ↓ 0.0 0 1

Subquery Scan on endfrom (cost=3,725,180.25..12,626,102.02 rows=56,667 width=184) (actual time=65,367.588..65,367.588 rows=0 loops=1)

  • Buffers: shared hit=145 read=448328
4. 0.004 65,367.587 ↓ 0.0 0 1

Append (cost=3,725,180.25..12,625,535.35 rows=56,667 width=184) (actual time=65,367.587..65,367.587 rows=0 loops=1)

  • Buffers: shared hit=145 read=448328
5. 0.002 65,367.517 ↓ 0.0 0 1

GroupAggregate (cost=3,725,180.25..12,624,906.53 rows=56,515 width=127) (actual time=65,367.517..65,367.517 rows=0 loops=1)

  • Group Key: a.idproduto, b.dataentsai
  • Buffers: shared hit=145 read=448328
6. 0.012 65,367.515 ↓ 0.0 0 1

Sort (cost=3,725,180.25..3,725,321.54 rows=56,515 width=150) (actual time=65,367.515..65,367.515 rows=0 loops=1)

  • Sort Key: a.idproduto, b.dataentsai
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=145 read=448328
7. 0.003 65,367.503 ↓ 0.0 0 1

Hash Join (cost=70,514.02..3,720,719.42 rows=56,515 width=150) (actual time=65,367.503..65,367.503 rows=0 loops=1)

  • Hash Cond: ((a.idnfmaster)::text = (b.idnfmaster)::text)
  • Buffers: shared hit=145 read=448328
8. 19,497.644 65,367.500 ↓ 0.0 0 1

Seq Scan on nfdet a (cost=0.00..3,640,614.83 rows=57,362 width=121) (actual time=65,367.500..65,367.500 rows=0 loops=1)

  • Filter: ((cancelado = ANY ('{0,-1}'::integer[])) AND ((SubPlan 6) = 0))
  • Rows Removed by Filter: 11607843
  • Buffers: shared hit=145 read=448328
9.          

SubPlan (forSeq Scan)

10. 45,869.856 45,869.856 ↑ 1.0 1 11,467,464

Function Scan on proc_cfop_naomovimentaestoque (cost=0.25..0.26 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=11,467,464)

  • Buffers: shared hit=15
11. 0.000 0.000 ↓ 0.0 0

Hash (cost=53,847.49..53,847.49 rows=748,523 width=50) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Seq Scan on nfmaster b (cost=0.00..53,847.49 rows=748,523 width=50) (never executed)

  • Filter: ((situacao = ANY ('{0,-1}'::integer[])) AND (dataentsai < CURRENT_DATE))
13.          

SubPlan (forGroupAggregate)

14. 0.000 0.000 ↓ 0.0 0

Function Scan on proc_calcimposto (cost=142.41..157.41 rows=1,000 width=8) (never executed)

15.          

Initplan (forFunction Scan)

16. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=61.83..61.84 rows=1 width=32) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using nfdet_idnfmaster_indx on nfdet (cost=0.56..59.51 rows=185 width=20) (never executed)

  • Index Cond: ((idnfmaster)::text = (b.idnfmaster)::text)
  • Filter: (cancelado = 0)
18. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=60.44..60.45 rows=1 width=32) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using nfdet_idnfmaster_indx on nfdet nfdet_1 (cost=0.56..59.51 rows=185 width=8) (never executed)

  • Index Cond: ((idnfmaster)::text = (b.idnfmaster)::text)
  • Filter: (cancelado = 0)
20. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.84..9.93 rows=1 width=12) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..9.93 rows=1 width=12) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..9.77 rows=1 width=3) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..9.47 rows=1 width=8) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on fornecedores f (cost=0.00..1.02 rows=1 width=68) (never executed)

  • Filter: ((b.idclifor)::text = (idfornecedor)::text)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ceps on ceps cep (cost=0.42..8.44 rows=1 width=17) (never executed)

  • Index Cond: ((idcep)::text = (f.idcep)::text)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cidades on cidades cid (cost=0.28..0.30 rows=1 width=11) (never executed)

  • Index Cond: ((idcidade)::text = (cep.idcidade)::text)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using unq1_ufs on ufs uf (cost=0.14..0.16 rows=1 width=24) (never executed)

  • Index Cond: ((iduf)::text = (cid.iduf)::text)
28. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.84..9.91 rows=1 width=12) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..9.91 rows=1 width=12) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..9.75 rows=1 width=3) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..9.45 rows=1 width=8) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on usuarios u (cost=0.00..1.01 rows=1 width=68) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ceps on ceps cep_1 (cost=0.42..8.44 rows=1 width=17) (never executed)

  • Index Cond: ((idcep)::text = (u.idcep)::text)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cidades on cidades cid_1 (cost=0.28..0.30 rows=1 width=11) (never executed)

  • Index Cond: ((idcidade)::text = (cep_1.idcidade)::text)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using unq1_ufs on ufs uf_1 (cost=0.14..0.16 rows=1 width=24) (never executed)

  • Index Cond: ((iduf)::text = (cid_1.iduf)::text)
36. 0.002 0.045 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=15.61..15.66 rows=1 width=184) (actual time=0.045..0.045 rows=0 loops=1)

37. 0.002 0.043 ↓ 0.0 0 1

GroupAggregate (cost=15.61..15.64 rows=1 width=148) (actual time=0.043..0.043 rows=0 loops=1)

  • Group Key: inventarios.idproduto, inventarios.data
38. 0.032 0.041 ↓ 0.0 0 1

Sort (cost=15.61..15.62 rows=1 width=112) (actual time=0.041..0.041 rows=0 loops=1)

  • Sort Key: inventarios.idproduto, inventarios.data
  • Sort Method: quicksort Memory: 25kB
39. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on inventarios (cost=0.00..15.60 rows=1 width=112) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: ((inicial = 1) AND (data < CURRENT_DATE))
40. 0.001 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=14.72..17.46 rows=84 width=184) (actual time=0.009..0.009 rows=0 loops=1)

41. 0.005 0.008 ↓ 0.0 0 1

HashAggregate (cost=14.72..15.78 rows=84 width=120) (actual time=0.008..0.008 rows=0 loops=1)

  • Group Key: producao.idprodutoassociado, producao.dataprod
42. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on producao (cost=0.00..14.05 rows=90 width=92) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (dataprod < CURRENT_DATE)
43. 0.001 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=28.21..30.56 rows=67 width=184) (actual time=0.012..0.012 rows=0 loops=1)

44. 0.003 0.011 ↓ 0.0 0 1

HashAggregate (cost=28.21..29.22 rows=67 width=188) (actual time=0.011..0.011 rows=0 loops=1)

  • Group Key: det.idprodassocigrediente, p.dataprod
45. 0.003 0.008 ↓ 0.0 0 1

Hash Join (cost=15.18..27.71 rows=67 width=92) (actual time=0.008..0.008 rows=0 loops=1)

  • Hash Cond: ((det.idproducaomaster)::text = (p.idproducao)::text)
46. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on producaodet det (cost=0.00..12.00 rows=200 width=156) (actual time=0.004..0.005 rows=0 loops=1)

47. 0.000 0.000 ↓ 0.0 0

Hash (cost=14.05..14.05 rows=90 width=72) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on producao p (cost=0.00..14.05 rows=90 width=72) (never executed)

  • Filter: (dataprod < CURRENT_DATE)
Planning time : 4.083 ms