explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DMJ2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,373,535.21..1,373,683.82 rows=20 width=22) (actual rows= loops=)

  • Group Key: p.c_period_id
2.          

Initplan (for GroupAggregate)

3. 0.000 0.000 ↓ 0.0

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

  • Filter: (ad_client_id = ANY ('{1000039,0}'::numeric[]))
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,373,534.20..1,373,583.67 rows=19,787 width=14) (actual rows= loops=)

  • Sort Key: p.c_period_id
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=285,404.43..1,372,122.17 rows=19,787 width=14) (actual rows= loops=)

  • Hash Cond: (((SubPlan 14)) = p.c_period_id)
6. 0.000 0.000 ↓ 0.0

Append (cost=285,341.83..1,369,140.99 rows=197,870 width=264) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=285,341.83..1,339,065.73 rows=196,060 width=199) (actual rows= loops=)

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

Initplan (for Hash Join)

9. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_InOut'::text)
10. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_InOutLine'::text)
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=285,310.69..676,596.86 rows=196,060 width=57) (actual rows= loops=)

  • Hash Cond: (il.m_inout_id = io.m_inout_id)
12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on m_inoutline il (cost=3,690.92..391,539.25 rows=196,966 width=24) (actual rows= loops=)

  • Recheck Cond: (m_product_id = '2002700'::numeric)
13. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (m_product_id = '2002700'::numeric)
14. 0.000 0.000 ↓ 0.0

Hash (cost=227,738.87..227,738.87 rows=4,310,472 width=39) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on m_inout io (cost=0.00..227,738.87 rows=4,310,472 width=39) (actual rows= loops=)

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
16. 0.000 0.000 ↓ 0.0

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

17. 0.000 0.000 ↓ 0.0

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

18.          

SubPlan (for Hash Join)

19. 0.000 0.000 ↓ 0.0

Seq Scan on c_period c_period_4 (cost=0.00..3.35 rows=7 width=7) (actual rows= loops=)

  • Filter: ((ad_client_id = io.ad_client_id) AND ((io.movementdate)::date >= startdate) AND ((io.movementdate)::date <= enddate))
20. 0.000 0.000 ↓ 0.0

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

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=956.58..13,983.15 rows=904 width=144) (actual rows= loops=)

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

Initplan (for Hash Join)

23. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_Movement'::text)
24. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_MovementLine'::text)
25. 0.000 0.000 ↓ 0.0

Hash Join (cost=925.44..10,911.18 rows=904 width=54) (actual rows= loops=)

  • Hash Cond: (ml.m_movement_id = m.m_movement_id)
26. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (m_product_id = '2002700'::numeric)
27. 0.000 0.000 ↓ 0.0

Hash (cost=731.90..731.90 rows=15,449 width=36) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

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

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

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

31.          

SubPlan (for Hash Join)

32. 0.000 0.000 ↓ 0.0

Seq Scan on c_period c_period_3 (cost=0.00..3.35 rows=7 width=7) (actual rows= loops=)

  • Filter: ((ad_client_id = m.ad_client_id) AND ((m.movementdate)::date >= startdate) AND ((m.movementdate)::date <= enddate))
33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

Hash Join (cost=956.58..13,985.41 rows=904 width=171) (actual rows= loops=)

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

Initplan (for Hash Join)

36. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_Movement'::text)
37. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_MovementLine'::text)
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=925.44..10,911.18 rows=904 width=54) (actual rows= loops=)

  • Hash Cond: (ml_1.m_movement_id = m_1.m_movement_id)
39. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (m_product_id = '2002700'::numeric)
40. 0.000 0.000 ↓ 0.0

Hash (cost=731.90..731.90 rows=15,449 width=36) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

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

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
42. 0.000 0.000 ↓ 0.0

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

43. 0.000 0.000 ↓ 0.0

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

44.          

SubPlan (for Hash Join)

45. 0.000 0.000 ↓ 0.0

Seq Scan on c_period c_period_2 (cost=0.00..3.35 rows=7 width=7) (actual rows= loops=)

  • Filter: ((ad_client_id = m_1.ad_client_id) AND ((m_1.movementdate)::date >= startdate) AND ((m_1.movementdate)::date <= enddate))
46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=17.14..75.42 rows=1 width=246) (actual rows= loops=)

47.          

Initplan (for Nested Loop)

48. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_Inventory'::text)
49. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((tablename)::text = 'M_InventoryLine'::text)
50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.41..47.27 rows=1 width=97) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

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

  • Filter: ((isactive = 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL,RE}'::bpchar[])))
52. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((m_inventory_id = i.m_inventory_id) AND (m_product_id = '2002700'::numeric))
53. 0.000 0.000 ↓ 0.0

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

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

SubPlan (for Nested Loop)

55. 0.000 0.000 ↓ 0.0

Seq Scan on c_period c_period_1 (cost=0.00..3.35 rows=7 width=7) (actual rows= loops=)

  • Filter: ((ad_client_id = il_1.ad_client_id) AND ((i.movementdate)::date >= startdate) AND ((i.movementdate)::date <= enddate))
56. 0.000 0.000 ↓ 0.0

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

57.          

Initplan (for Nested Loop)

58. 0.000 0.000 ↓ 0.0

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

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

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

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

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

61. 0.000 0.000 ↓ 0.0

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

62. 0.000 0.000 ↓ 0.0

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

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

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

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

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

  • 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

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

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

SubPlan (for Nested Loop)

67. 0.000 0.000 ↓ 0.0

Seq Scan on c_period (cost=0.00..3.35 rows=7 width=7) (actual rows= loops=)

  • Filter: ((ad_client_id = pl.ad_client_id) AND ((p_1.movementdate)::date >= startdate) AND ((p_1.movementdate)::date <= enddate))
68. 0.000 0.000 ↓ 0.0

Hash (cost=62.35..62.35 rows=20 width=21) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash Join (cost=59.03..62.35 rows=20 width=21) (actual rows= loops=)

  • Hash Cond: (p.c_period_id = c_periodcontrol.c_period_id)
70. 0.000 0.000 ↓ 0.0

Seq Scan on c_period p (cost=0.00..3.05 rows=20 width=14) (actual rows= loops=)

  • Filter: ((startdate >= $0) AND (isactive = 'Y'::bpchar) AND (ad_client_id = '1000039'::numeric))
71. 0.000 0.000 ↓ 0.0

Hash (cost=58.28..58.28 rows=60 width=7) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

HashAggregate (cost=57.68..58.28 rows=60 width=7) (actual rows= loops=)

  • Group Key: c_periodcontrol.c_period_id
73. 0.000 0.000 ↓ 0.0

Seq Scan on c_periodcontrol (cost=0.00..56.26 rows=565 width=7) (actual rows= loops=)

  • Filter: (periodstatus <> 'C'::bpchar)