explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7,661.798 302,536.336 ↓ 691.1 3,916,310 1

GroupAggregate (cost=12,630,559.26..12,631,805.94 rows=5,667 width=208) (actual time=292,366.877..302,536.336 rows=3,916,310 loops=1)

  • Group Key: endfrom.idproduto, endfrom.dataentsai
  • Buffers: shared hit=4589810 read=501833, temp read=548029 written=548204
2. 5,078.449 294,874.538 ↓ 69.1 3,916,310 1

Sort (cost=12,630,559.26..12,630,700.93 rows=56,667 width=184) (actual time=292,366.856..294,874.538 rows=3,916,310 loops=1)

  • Sort Key: endfrom.idproduto, endfrom.dataentsai
  • Sort Method: external merge Disk: 216464kB
  • Buffers: shared hit=4589810 read=501833, temp read=548029 written=548204
3. 784.716 289,796.089 ↓ 69.1 3,916,310 1

Subquery Scan on endfrom (cost=3,725,163.57..12,626,085.34 rows=56,667 width=184) (actual time=176,303.614..289,796.089 rows=3,916,310 loops=1)

  • Buffers: shared hit=4589810 read=501833, temp read=520971 written=521121
4. 545.282 289,011.373 ↓ 69.1 3,916,310 1

Append (cost=3,725,163.57..12,625,518.67 rows=56,667 width=184) (actual time=176,303.611..289,011.373 rows=3,916,310 loops=1)

  • Buffers: shared hit=4589810 read=501833, temp read=520971 written=521121
5. 25,312.706 288,466.046 ↓ 69.3 3,916,310 1

GroupAggregate (cost=3,725,163.57..12,624,889.85 rows=56,515 width=127) (actual time=176,303.610..288,466.046 rows=3,916,310 loops=1)

  • Group Key: a.idproduto, b.dataentsai
  • Buffers: shared hit=4589810 read=501833, temp read=520971 written=521121
6. 82,367.950 185,174.172 ↓ 202.8 11,460,615 1

Sort (cost=3,725,163.57..3,725,304.86 rows=56,515 width=150) (actual time=176,303.554..185,174.172 rows=11,460,615 loops=1)

  • Sort Key: a.idproduto, b.dataentsai
  • Sort Method: external merge Disk: 1952808kB
  • Buffers: shared hit=25535 read=463475, temp read=520971 written=521121
7. 23,678.703 102,806.222 ↓ 202.8 11,460,615 1

Hash Join (cost=70,500.35..3,720,702.75 rows=56,515 width=150) (actual time=1,427.541..102,806.222 rows=11,460,615 loops=1)

  • Hash Cond: ((a.idnfmaster)::text = (b.idnfmaster)::text)
  • Buffers: shared hit=25535 read=463475, temp read=157621 written=157615
8. 31,845.509 77,715.365 ↓ 199.9 11,465,155 1

Seq Scan on nfdet a (cost=0.00..3,640,614.83 rows=57,362 width=121) (actual time=14.834..77,715.365 rows=11,465,155 loops=1)

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

SubPlan (forSeq Scan)

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

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=11,467,464)

11. 438.715 1,412.154 ↑ 1.0 747,637 1

Hash (cost=53,841.22..53,841.22 rows=748,170 width=50) (actual time=1,412.154..1,412.154 rows=747,637 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 17890kB
  • Buffers: shared hit=3 read=40549, temp written=5087
12. 973.439 973.439 ↑ 1.0 747,637 1

Seq Scan on nfmaster b (cost=0.00..53,841.22 rows=748,170 width=50) (actual time=0.024..973.439 rows=747,637 loops=1)

  • Filter: ((situacao = ANY ('{0,-1}'::integer[])) AND (dataentsai < CURRENT_DATE))
  • Rows Removed by Filter: 11747
  • Buffers: shared hit=3 read=40549
13.          

SubPlan (forGroupAggregate)

14. 17,835.832 77,979.168 ↓ 0.0 0 270,761

Function Scan on proc_calcimposto (cost=142.41..157.41 rows=1,000 width=8) (actual time=0.288..0.288 rows=0 loops=270,761)

  • Buffers: shared hit=4564275 read=38358
15.          

Initplan (forFunction Scan)

16. 11,371.962 46,570.892 ↑ 1.0 1 270,761

Aggregate (cost=61.83..61.84 rows=1 width=32) (actual time=0.172..0.172 rows=1 loops=270,761)

  • Buffers: shared hit=2127578 read=38353
17. 35,198.930 35,198.930 ↑ 4.7 39 270,761

Index Scan using nfdet_idnfmaster_indx on nfdet (cost=0.56..59.51 rows=185 width=20) (actual time=0.103..0.130 rows=39 loops=270,761)

  • Index Cond: ((idnfmaster)::text = (b.idnfmaster)::text)
  • Filter: (cancelado = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2127578 read=38353
18. 4,332.176 12,725.767 ↑ 1.0 1 270,761

Aggregate (cost=60.44..60.45 rows=1 width=32) (actual time=0.047..0.047 rows=1 loops=270,761)

  • Buffers: shared hit=2165931
19. 8,393.591 8,393.591 ↑ 4.7 39 270,761

Index Scan using nfdet_idnfmaster_indx on nfdet nfdet_1 (cost=0.56..59.51 rows=185 width=8) (actual time=0.015..0.031 rows=39 loops=270,761)

  • Index Cond: ((idnfmaster)::text = (b.idnfmaster)::text)
  • Filter: (cancelado = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2165931
20. 0.000 812.283 ↓ 0.0 0 270,761

Limit (cost=0.84..9.93 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=270,761)

  • Buffers: shared hit=270761
21. 270.761 812.283 ↓ 0.0 0 270,761

Nested Loop (cost=0.84..9.93 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=270,761)

  • Buffers: shared hit=270761
22. 0.000 541.522 ↓ 0.0 0 270,761

Nested Loop (cost=0.71..9.77 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=270,761)

  • Buffers: shared hit=270761
23. 270.761 541.522 ↓ 0.0 0 270,761

Nested Loop (cost=0.42..9.47 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=270,761)

  • Buffers: shared hit=270761
24. 270.761 270.761 ↓ 0.0 0 270,761

Seq Scan on fornecedores f (cost=0.00..1.02 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=270,761)

  • Filter: ((b.idclifor)::text = (idfornecedor)::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=270761
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.008 34.394 ↑ 1.0 1 1

Limit (cost=0.84..9.91 rows=1 width=12) (actual time=34.388..34.394 rows=1 loops=1)

  • Buffers: shared hit=5 read=5
29. 0.005 34.386 ↑ 1.0 1 1

Nested Loop (cost=0.84..9.91 rows=1 width=12) (actual time=34.386..34.386 rows=1 loops=1)

  • Buffers: shared hit=5 read=5
30. 0.009 29.988 ↑ 1.0 1 1

Nested Loop (cost=0.71..9.75 rows=1 width=3) (actual time=29.987..29.988 rows=1 loops=1)

  • Buffers: shared hit=4 read=4
31. 0.009 21.527 ↑ 1.0 1 1

Nested Loop (cost=0.42..9.45 rows=1 width=8) (actual time=21.526..21.527 rows=1 loops=1)

  • Buffers: shared hit=2 read=3
32. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on usuarios u (cost=0.00..1.01 rows=1 width=68) (actual time=0.007..0.007 rows=1 loops=1)

  • Buffers: shared hit=1
33. 21.511 21.511 ↑ 1.0 1 1

Index Scan using pk_ceps on ceps cep_1 (cost=0.42..8.44 rows=1 width=17) (actual time=21.511..21.511 rows=1 loops=1)

  • Index Cond: ((idcep)::text = (u.idcep)::text)
  • Buffers: shared hit=1 read=3
34. 8.452 8.452 ↑ 1.0 1 1

Index Scan using pk_cidades on cidades cid_1 (cost=0.28..0.30 rows=1 width=11) (actual time=8.452..8.452 rows=1 loops=1)

  • Index Cond: ((idcidade)::text = (cep_1.idcidade)::text)
  • Buffers: shared hit=2 read=1
35. 4.393 4.393 ↑ 1.0 1 1

Index Scan using unq1_ufs on ufs uf_1 (cost=0.14..0.16 rows=1 width=24) (actual time=4.393..4.393 rows=1 loops=1)

  • Index Cond: ((iduf)::text = (cid_1.iduf)::text)
  • Buffers: shared hit=1 read=1
36. 0.001 0.027 ↓ 0.0 0 1

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

37. 0.002 0.026 ↓ 0.0 0 1

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

  • Group Key: inventarios.idproduto, inventarios.data
38. 0.021 0.024 ↓ 0.0 0 1

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

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

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

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

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

41. 0.004 0.007 ↓ 0.0 0 1

HashAggregate (cost=14.72..15.78 rows=84 width=120) (actual time=0.007..0.007 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.010 ↓ 0.0 0 1

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

44. 0.004 0.009 ↓ 0.0 0 1

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

  • Group Key: det.idprodassocigrediente, p.dataprod
45. 0.002 0.005 ↓ 0.0 0 1

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

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

Seq Scan on producaodet det (cost=0.00..12.00 rows=200 width=156) (actual time=0.003..0.003 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 : 229.471 ms