explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7TZw

Settings
# exclusive inclusive rows x rows loops node
1. 3.873 8,031.605 ↑ 2.9 7 1

GroupAggregate (cost=1,373,535.21..1,373,683.82 rows=20 width=22) (actual time=8,027.156..8,031.605 rows=7 loops=1)

  • Group Key: p.c_period_id
2.          

Initplan (forGroupAggregate)

3. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on m_materialpolicy (cost=0.00..1.01 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

  • Filter: (ad_client_id = ANY ('{1000039,0}'::numeric[]))
4. 11.264 8,027.707 ↓ 1.5 28,803 1

Sort (cost=1,373,534.20..1,373,583.67 rows=19,787 width=14) (actual time=8,026.244..8,027.707 rows=28,803 loops=1)

  • Sort Key: p.c_period_id
  • Sort Method: quicksort Memory: 2119kB
5. 30.384 8,016.443 ↓ 1.5 28,803 1

Hash Join (cost=285,404.43..1,372,122.17 rows=19,787 width=14) (actual time=3,129.254..8,016.443 rows=28,803 loops=1)

  • Hash Cond: (((SubPlan 14)) = p.c_period_id)
6. 14.827 7,985.411 ↓ 1.1 214,262 1

Append (cost=285,341.83..1,369,140.99 rows=197,870 width=264) (actual time=2,773.648..7,985.411 rows=214,262 loops=1)

7. 363.337 7,878.009 ↓ 1.1 212,229 1

Hash Join (cost=285,341.83..1,339,065.73 rows=196,060 width=199) (actual time=2,773.647..7,878.009 rows=212,229 loops=1)

  • Hash Cond: (io.c_doctype_id = dt.c_doctype_id)
8.          

Initplan (forHash Join)

9. 0.045 0.045 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_8 (cost=0.28..8.29 rows=1 width=5) (actual time=0.044..0.045 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_InOut'::text)
10. 0.020 0.020 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_9 (cost=0.28..8.29 rows=1 width=5) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_InOutLine'::text)
11. 182.678 3,694.367 ↓ 1.1 212,229 1

Hash Join (cost=285,310.69..676,596.86 rows=196,060 width=57) (actual time=2,773.406..3,694.367 rows=212,229 loops=1)

  • Hash Cond: (il.m_inout_id = io.m_inout_id)
12. 792.780 832.510 ↓ 1.1 213,194 1

Bitmap Heap Scan on m_inoutline il (cost=3,690.92..391,539.25 rows=196,966 width=24) (actual time=74.457..832.510 rows=213,194 loops=1)

  • Recheck Cond: (m_product_id = '2002700'::numeric)
  • Heap Blocks: exact=161938
13. 39.730 39.730 ↓ 1.1 213,194 1

Bitmap Index Scan on m_inoutline_product (cost=0.00..3,641.68 rows=196,966 width=0) (actual time=39.730..39.730 rows=213,194 loops=1)

  • Index Cond: (m_product_id = '2002700'::numeric)
14. 1,163.154 2,679.179 ↓ 1.0 4,313,636 1

Hash (cost=227,738.87..227,738.87 rows=4,310,472 width=39) (actual time=2,679.179..2,679.179 rows=4,313,636 loops=1)

  • Buckets: 8388608 Batches: 1 Memory Usage: 373051kB
15. 1,516.025 1,516.025 ↓ 1.0 4,313,636 1

Seq Scan on m_inout io (cost=0.00..227,738.87 rows=4,310,472 width=39) (actual time=0.034..1,516.025 rows=4,313,636 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
  • Rows Removed by Filter: 16756
16. 0.039 0.118 ↑ 1.0 158 1

Hash (cost=12.58..12.58 rows=158 width=14) (actual time=0.118..0.118 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
17. 0.079 0.079 ↑ 1.0 158 1

Seq Scan on c_doctype dt (cost=0.00..12.58 rows=158 width=14) (actual time=0.009..0.079 rows=158 loops=1)

18.          

SubPlan (forHash Join)

19. 3,820.122 3,820.122 ↑ 7.0 1 212,229

Seq Scan on c_period c_period_4 (cost=0.00..3.35 rows=7 width=7) (actual time=0.010..0.018 rows=1 loops=212,229)

  • Filter: ((ad_client_id = io.ad_client_id) AND ((io.movementdate)::date >= startdate) AND ((io.movementdate)::date <= enddate))
  • Rows Removed by Filter: 59
20. 0.209 44.121 ↓ 1.1 1,006 1

Subquery Scan on *SELECT* 2 (cost=956.58..13,994.45 rows=904 width=172) (actual time=8.793..44.121 rows=1,006 loops=1)

21. 1.106 43.912 ↓ 1.1 1,006 1

Hash Join (cost=956.58..13,983.15 rows=904 width=144) (actual time=8.787..43.912 rows=1,006 loops=1)

  • Hash Cond: (m.c_doctype_id = dt_1.c_doctype_id)
22.          

Initplan (forHash Join)

23. 0.013 0.013 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_6 (cost=0.28..8.29 rows=1 width=5) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_Movement'::text)
24. 0.004 0.004 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_7 (cost=0.28..8.29 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_MovementLine'::text)
25. 0.507 24.588 ↓ 1.1 1,006 1

Hash Join (cost=925.44..10,911.18 rows=904 width=54) (actual time=8.639..24.588 rows=1,006 loops=1)

  • Hash Cond: (ml.m_movement_id = m.m_movement_id)
26. 15.476 15.476 ↑ 1.0 1,006 1

Index Scan using m_movementline_carding on m_movementline ml (cost=0.42..9,973.29 rows=1,022 width=25) (actual time=0.024..15.476 rows=1,006 loops=1)

  • Index Cond: (m_product_id = '2002700'::numeric)
27. 2.727 8.605 ↑ 1.0 15,447 1

Hash (cost=731.90..731.90 rows=15,449 width=36) (actual time=8.605..8.605 rows=15,447 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1068kB
28. 5.878 5.878 ↑ 1.0 15,447 1

Seq Scan on m_movement m (cost=0.00..731.90 rows=15,449 width=36) (actual time=0.010..5.878 rows=15,447 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
  • Rows Removed by Filter: 2024
29. 0.025 0.093 ↑ 1.0 158 1

Hash (cost=12.58..12.58 rows=158 width=14) (actual time=0.093..0.093 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
30. 0.068 0.068 ↑ 1.0 158 1

Seq Scan on c_doctype dt_1 (cost=0.00..12.58 rows=158 width=14) (actual time=0.005..0.068 rows=158 loops=1)

31.          

SubPlan (forHash Join)

32. 18.108 18.108 ↑ 7.0 1 1,006

Seq Scan on c_period c_period_3 (cost=0.00..3.35 rows=7 width=7) (actual time=0.010..0.018 rows=1 loops=1,006)

  • Filter: ((ad_client_id = m.ad_client_id) AND ((m.movementdate)::date >= startdate) AND ((m.movementdate)::date <= enddate))
  • Rows Removed by Filter: 59
33. 0.180 43.477 ↓ 1.1 1,006 1

Subquery Scan on *SELECT* 3 (cost=956.58..13,996.71 rows=904 width=199) (actual time=8.611..43.477 rows=1,006 loops=1)

34. 1.319 43.297 ↓ 1.1 1,006 1

Hash Join (cost=956.58..13,985.41 rows=904 width=171) (actual time=8.609..43.297 rows=1,006 loops=1)

  • Hash Cond: (m_1.c_doctype_id = dt_2.c_doctype_id)
35.          

Initplan (forHash Join)

36. 0.006 0.006 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_4 (cost=0.28..8.29 rows=1 width=5) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_Movement'::text)
37. 0.003 0.003 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_5 (cost=0.28..8.29 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_MovementLine'::text)
38. 0.518 23.767 ↓ 1.1 1,006 1

Hash Join (cost=925.44..10,911.18 rows=904 width=54) (actual time=8.470..23.767 rows=1,006 loops=1)

  • Hash Cond: (ml_1.m_movement_id = m_1.m_movement_id)
39. 14.839 14.839 ↑ 1.0 1,006 1

Index Scan using m_movementline_carding on m_movementline ml_1 (cost=0.42..9,973.29 rows=1,022 width=25) (actual time=0.012..14.839 rows=1,006 loops=1)

  • Index Cond: (m_product_id = '2002700'::numeric)
40. 2.710 8.410 ↑ 1.0 15,447 1

Hash (cost=731.90..731.90 rows=15,449 width=36) (actual time=8.410..8.410 rows=15,447 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1068kB
41. 5.700 5.700 ↑ 1.0 15,447 1

Seq Scan on m_movement m_1 (cost=0.00..731.90 rows=15,449 width=36) (actual time=0.006..5.700 rows=15,447 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
  • Rows Removed by Filter: 2024
42. 0.023 0.094 ↑ 1.0 158 1

Hash (cost=12.58..12.58 rows=158 width=14) (actual time=0.094..0.094 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
43. 0.071 0.071 ↑ 1.0 158 1

Seq Scan on c_doctype dt_2 (cost=0.00..12.58 rows=158 width=14) (actual time=0.005..0.071 rows=158 loops=1)

44.          

SubPlan (forHash Join)

45. 18.108 18.108 ↑ 7.0 1 1,006

Seq Scan on c_period c_period_2 (cost=0.00..3.35 rows=7 width=7) (actual time=0.010..0.018 rows=1 loops=1,006)

  • Filter: ((ad_client_id = m_1.ad_client_id) AND ((m_1.movementdate)::date >= startdate) AND ((m_1.movementdate)::date <= enddate))
  • Rows Removed by Filter: 59
46. 0.067 4.975 ↓ 21.0 21 1

Nested Loop (cost=17.14..75.42 rows=1 width=246) (actual time=0.096..4.975 rows=21 loops=1)

47.          

Initplan (forNested Loop)

48. 0.008 0.008 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_2 (cost=0.28..8.29 rows=1 width=5) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_Inventory'::text)
49. 0.005 0.005 ↑ 1.0 1 1

Index Scan using ad_table_name on ad_table ad_table_3 (cost=0.28..8.29 rows=1 width=5) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((tablename)::text = 'M_InventoryLine'::text)
50. 0.036 4.454 ↓ 21.0 21 1

Nested Loop (cost=0.41..47.27 rows=1 width=97) (actual time=0.041..4.454 rows=21 loops=1)

51. 0.032 0.032 ↓ 43.0 43 1

Seq Scan on m_inventory i (cost=0.00..2.75 rows=1 width=64) (actual time=0.012..0.032 rows=43 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
  • Rows Removed by Filter: 3
52. 4.386 4.386 ↓ 0.0 0 43

Index Scan using m_inventoryline_productlocattr on m_inventoryline il_1 (cost=0.41..44.52 rows=1 width=40) (actual time=0.016..0.102 rows=0 loops=43)

  • Index Cond: ((m_inventory_id = i.m_inventory_id) AND (m_product_id = '2002700'::numeric))
53. 0.042 0.042 ↑ 1.0 1 21

Index Scan using testdoctype on c_doctype dt_3 (cost=0.14..8.16 rows=1 width=14) (actual time=0.001..0.002 rows=1 loops=21)

  • Index Cond: (c_doctype_id = i.c_doctype_id)
54.          

SubPlan (forNested Loop)

55. 0.399 0.399 ↑ 7.0 1 21

Seq Scan on c_period c_period_1 (cost=0.00..3.35 rows=7 width=7) (actual time=0.011..0.019 rows=1 loops=21)

  • Filter: ((ad_client_id = il_1.ad_client_id) AND ((i.movementdate)::date >= startdate) AND ((i.movementdate)::date <= enddate))
  • Rows Removed by Filter: 59
56. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=17.01..48.07 rows=1 width=248) (actual time=0.002..0.002 rows=0 loops=1)

57.          

Initplan (forNested Loop)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using ad_table_name on ad_table (cost=0.28..8.29 rows=1 width=5) (never executed)

  • Index Cond: ((tablename)::text = 'M_Production'::text)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using ad_table_name on ad_table ad_table_1 (cost=0.28..8.29 rows=1 width=5) (never executed)

  • Index Cond: ((tablename)::text = 'M_ProductionLine'::text)
60. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.28..19.96 rows=1 width=160) (actual time=0.002..0.002 rows=0 loops=1)

61. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.14..19.29 rows=1 width=112) (actual time=0.002..0.002 rows=0 loops=1)

62. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on m_productionline pl (cost=0.00..11.12 rows=1 width=96) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (m_product_id = '2002700'::numeric)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using m_productionplan_pkey on m_productionplan pla (cost=0.14..8.16 rows=1 width=32) (never executed)

  • Index Cond: (m_productionplan_id = pl.m_productionplan_id)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using m_production_pkey on m_production p_1 (cost=0.14..0.65 rows=1 width=64) (never executed)

  • Index Cond: (m_production_id = pla.m_production_id)
  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
65. 0.000 0.000 ↓ 0.0 0

Index Scan using testdoctype on c_doctype dt_4 (cost=0.14..8.16 rows=1 width=14) (never executed)

  • Index Cond: (c_doctype_id = p_1.c_doctype_id)
66.          

SubPlan (forNested Loop)

67. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_period (cost=0.00..3.35 rows=7 width=7) (never executed)

  • Filter: ((ad_client_id = pl.ad_client_id) AND ((p_1.movementdate)::date >= startdate) AND ((p_1.movementdate)::date <= enddate))
68. 0.007 0.648 ↑ 1.3 15 1

Hash (cost=62.35..62.35 rows=20 width=21) (actual time=0.648..0.648 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.012 0.641 ↑ 1.3 15 1

Hash Join (cost=59.03..62.35 rows=20 width=21) (actual time=0.623..0.641 rows=15 loops=1)

  • Hash Cond: (p.c_period_id = c_periodcontrol.c_period_id)
70. 0.050 0.050 ↓ 2.4 48 1

Seq Scan on c_period p (cost=0.00..3.05 rows=20 width=14) (actual time=0.033..0.050 rows=48 loops=1)

  • Filter: ((startdate >= $0) AND (isactive = 'Y'::bpchar) AND (ad_client_id = '1000039'::numeric))
  • Rows Removed by Filter: 12
71. 0.006 0.579 ↑ 2.9 21 1

Hash (cost=58.28..58.28 rows=60 width=7) (actual time=0.579..0.579 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 0.163 0.573 ↑ 2.9 21 1

HashAggregate (cost=57.68..58.28 rows=60 width=7) (actual time=0.570..0.573 rows=21 loops=1)

  • Group Key: c_periodcontrol.c_period_id
73. 0.410 0.410 ↓ 1.1 596 1

Seq Scan on c_periodcontrol (cost=0.00..56.26 rows=565 width=7) (actual time=0.009..0.410 rows=596 loops=1)

  • Filter: (periodstatus <> 'C'::bpchar)
  • Rows Removed by Filter: 1265
Planning time : 4.280 ms
Execution time : 8,035.723 ms