explain.depesz.com

A tool for finding a real cause for slow queries.

Result: gQa

options
# exclusive inclusive rows x rows loops node
1. 12.884 3325.454 ↑ 340.8 5 1
→

GroupAggregate (cost=77356.78..77505.88 rows=1704 width=62) (actual time=3307.008..3325.454 rows=5 loops=1)

2. 37.984 3312.570 ↓ 17.9 30447 1
→

Sort (cost=77356.78..77361.04 rows=1704 width=62) (actual time=3306.052..3312.570 rows=30447 loops=1)

  • Sort Key: (date_trunc('month'::text, to_timestamp(((dd.order_date)::text || lpad((dd.order_time)::text, 6, '0'::text)), 'YYYYMMDDHH24MISS'::text)))
  • Sort Method: external merge Disk: 1168kB
3. 114.417 3274.586 ↓ 17.9 30447 1
→

Hash Join (cost=24738.16..77265.32 rows=1704 width=62) (actual time=516.618..3274.586 rows=30447 loops=1)

  • Hash Cond: ((m.code)::text = (m.code)::text)
4. 0.178 2792.255 ↓ 3.5 30447 1
→

Nested Loop (cost=6610.32..59010.02 rows=8723 width=81) (actual time=148.657..2792.255 rows=30447 loops=1)

5. 18.238 2731.183 ↓ 3.5 30447 1
→

Nested Loop (cost=6610.32..56401.82 rows=8723 width=76) (actual time=148.652..2731.183 rows=30447 loops=1)

6. 9.063 2652.051 ↓ 3.5 30447 1
→

Hash Left Join (cost=6610.32..53793.61 rows=8723 width=80) (actual time=148.587..2652.051 rows=30447 loops=1)

  • Hash Cond: (hr.miss_root_cause_id = mrc.id)
7. 7.596 2642.956 ↓ 3.5 30447 1
→

Hash Left Join (cost=6608.62..53747.75 rows=8723 width=43) (actual time=148.540..2642.956 rows=30447 loops=1)

  • Hash Cond: (hr.alt_site_id = om1.id)
8. 88.686 2634.642 ↓ 3.5 30447 1
→

Hash Join (cost=6521.94..53628.35 rows=8723 width=47) (actual time=147.795..2634.642 rows=30447 loops=1)

  • Hash Cond: ((dd.ddemand_key)::text = (hr.ddemand_key)::text)
9. 2400.890 2400.890 ↑ 1.1 42575 1
→

Seq Scan on daily_demand dd (cost=0.00..44995.62 rows=45207 width=37) (actual time=0.156..2400.890 rows=42575 loops=1)

  • Filter: ((to_timestamp(((order_date)::text || lpad((order_time)::text, 6, '0'::text)), 'YYYYMMDDHH24MISS'::text) <= ('now'::text)::date) AND (date_trunc('month'::text, to_timestamp(((order_date)::text || lpad((order_time)::text, 6, '0'::text)), 'YYYYMMDDHH24MISS'::text)) > (date_trunc('month'::text, (('now'::text)::date)::timestamp with time zone) - '6 mons'::interval)))
10. 52.514 145.066 ↓ 1.5 114430 1
→

Hash (cost=4620.63..4620.63 rows=78505 width=71) (actual time=145.066..145.066 rows=114430 loops=1)

11. 59.114 92.552 ↓ 1.5 114430 1
→

Hash Join (cost=114.47..4620.63 rows=78505 width=71) (actual time=1.716..92.552 rows=114430 loops=1)

  • Hash Cond: (hr.site_id = om.id)
12. 31.787 31.787 ↑ 1.0 134626 1
→

Seq Scan on hit_rate hr (cost=0.00..3216.26 rows=134626 width=65) (actual time=0.042..31.787 rows=134626 loops=1)

13. 0.133 1.651 ↑ 1.0 484 1
→

Hash (cost=108.42..108.42 rows=484 width=14) (actual time=1.651..1.651 rows=484 loops=1)

14. 0.283 1.518 ↑ 1.0 484 1
→

Nested Loop (cost=25.18..108.42 rows=484 width=14) (actual time=0.675..1.518 rows=484 loops=1)

15. 0.002 0.199 ↑ 1.0 1 1
→

Subquery Scan vpr (cost=25.18..25.21 rows=1 width=32) (actual time=0.198..0.199 rows=1 loops=1)

  • Filter: ((vpr.region_code)::text = 'DLR-US-FLD'::text)
16. 0.018 0.197 ↑ 1.0 2 1
→

Sort (cost=25.18..25.18 rows=2 width=24) (actual time=0.197..0.197 rows=2 loops=1)

  • Sort Key: log_div_mast.description
  • Sort Method: quicksort Memory: 25kB
17. 0.001 0.179 ↑ 1.0 2 1
→

Append (cost=0.00..25.17 rows=2 width=24) (actual time=0.124..0.179 rows=2 loops=1)

18. 0.002 0.166 ↑ 1.0 1 1
→

Nested Loop (cost=0.00..16.90 rows=1 width=33) (actual time=0.123..0.166 rows=1 loops=1)

  • Join Filter: (sys_co_regs.cid = plan.sys_user.cid)
19. 0.002 0.091 ↑ 1.0 1 1
→

Nested Loop (cost=0.00..8.63 rows=1 width=26) (actual time=0.055..0.091 rows=1 loops=1)

  • Join Filter: ((log_div_mast.log_div)::text = (sys_co_regs.region)::text)
20. 0.006 0.077 ↑ 1.0 1 1
→

Nested Loop (cost=0.00..7.36 rows=1 width=31) (actual time=0.044..0.077 rows=1 loops=1)

  • Join Filter: ((sys_user_regions.region)::text = (log_div_mast.log_div)::text)
21. 0.059 0.059 ↑ 1.0 1 1
→

Seq Scan on sys_user_regions (cost=0.00..6.05 rows=1 width=13) (actual time=0.028..0.059 rows=1 loops=1)

  • Filter: (uid = 55)
22. 0.012 0.012 ↑ 1.0 14 1
→

Seq Scan on log_div_mast (cost=0.00..1.14 rows=14 width=18) (actual time=0.011..0.012 rows=14 loops=1)

23. 0.012 0.012 ↑ 1.0 12 1
→

Seq Scan on sys_co_regs (cost=0.00..1.12 rows=12 width=13) (actual time=0.010..0.012 rows=12 loops=1)

24. 0.073 0.073 ↑ 1.0 1 1
→

Seq Scan on sys_user (cost=0.00..8.25 rows=1 width=15) (actual time=0.066..0.073 rows=1 loops=1)

  • Filter: (plan.sys_user.uid = 55)
25. 0.000 0.012 ↑ 1.0 1 1
→

Subquery Scan "*SELECT* 2" (cost=0.00..8.26 rows=1 width=15) (actual time=0.010..0.012 rows=1 loops=1)

26. 0.012 0.012 ↑ 1.0 1 1
→

Seq Scan on sys_user (cost=0.00..8.25 rows=1 width=15) (actual time=0.010..0.012 rows=1 loops=1)

  • Filter: (uid = 55)
27. 1.036 1.036 ↑ 1.0 484 1
→

Seq Scan on office_mast om (cost=0.00..78.38 rows=484 width=14) (actual time=0.474..1.036 rows=484 loops=1)

  • Filter: ((om.log_div)::text = 'DLR-US-FLD'::text)
28. 0.179 0.718 ↑ 1.0 830 1
→

Hash (cost=76.30..76.30 rows=830 width=4) (actual time=0.718..0.718 rows=830 loops=1)

29. 0.539 0.539 ↑ 1.0 830 1
→

Seq Scan on office_mast om1 (cost=0.00..76.30 rows=830 width=4) (actual time=0.015..0.539 rows=830 loops=1)

30. 0.012 0.032 ↑ 1.0 31 1
→

Hash (cost=1.31..1.31 rows=31 width=45) (actual time=0.032..0.032 rows=31 loops=1)

31. 0.020 0.020 ↑ 1.0 31 1
→

Seq Scan on miss_root_cause mrc (cost=0.00..1.31 rows=31 width=45) (actual time=0.015..0.020 rows=31 loops=1)

32. 60.894 60.894 ↑ 1.0 1 30447
→

Index Scan using material_pkey on material m1 (cost=0.00..0.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=30447)

  • Index Cond: (m1.id = hr.master_material_id)
33. 60.894 60.894 ↑ 1.0 1 30447
→

Index Scan using material_pkey on material m (cost=0.00..0.29 rows=1 width=13) (actual time=0.001..0.002 rows=1 loops=30447)

  • Index Cond: (m.id = hr.material_id)
34. 9.539 367.914 ↓ 2.7 27792 1
→

Hash (cost=17997.32..17997.32 rows=10442 width=18) (actual time=367.914..367.914 rows=27792 loops=1)

35. 12.300 358.375 ↓ 2.7 27792 1
→

Merge Left Join (cost=17869.60..17997.32 rows=10442 width=18) (actual time=335.682..358.375 rows=27792 loops=1)

  • Merge Cond: ((m.code)::text = (lma.mat_code)::text)
  • Join Filter: ((lma.log_div)::text = (ldm.log_div)::text)
36. 47.850 321.518 ↓ 2.7 27792 1
→

Sort (cost=16954.81..16980.91 rows=10442 width=18) (actual time=312.703..321.518 rows=27792 loops=1)

  • Sort Key: m.code
  • Sort Method: external merge Disk: 808kB
37. 6.918 273.668 ↓ 2.7 27792 1
→

Hash Left Join (cost=10648.96..16257.80 rows=10442 width=18) (actual time=109.006..273.668 rows=27792 loops=1)

  • Hash Cond: ((ira.dc_pc_id)::text = (dc_pc.pc_id)::text)
38. 6.884 266.729 ↓ 2.7 27792 1
→

Hash Left Join (cost=10646.95..16112.21 rows=10442 width=38) (actual time=108.967..266.729 rows=27792 loops=1)

  • Hash Cond: ((ira.supply_pc_id)::text = (supply_pc.pc_id)::text)
39. 11.080 259.824 ↓ 2.7 27792 1
→

Hash Left Join (cost=10644.93..15966.62 rows=10442 width=58) (actual time=108.930..259.824 rows=27792 loops=1)

  • Hash Cond: ((ira.field_pc_id)::text = (field_pc.pc_id)::text)
40. 6.928 248.701 ↓ 2.7 27792 1
→

Hash Left Join (cost=10642.92..15821.03 rows=10442 width=82) (actual time=108.871..248.701 rows=27792 loops=1)

  • Hash Cond: (((ldm.log_div)::text = (lcm.log_div)::text) AND ((ma.com_code)::text = (lcm.com_code)::text))
41. 10.093 241.738 ↓ 2.7 27792 1
→

Hash Left Join (cost=10638.49..15738.02 rows=10442 width=83) (actual time=108.821..241.738 rows=27792 loops=1)

  • Hash Cond: (((ldm.log_div)::text = (igm.region)::text) AND ((ma.part_group)::text = (igm.item_group_code)::text))
42. 5.438 231.609 ↓ 2.7 27792 1
→

Merge Left Join (cost=10635.81..15519.80 rows=10442 width=85) (actual time=108.763..231.609 rows=27792 loops=1)

  • Merge Cond: (scr.cid = cma.cid)
  • Join Filter: (cma.material_id = m.id)
43. 5.417 226.150 ↓ 2.7 27792 1
→

Nested Loop (cost=10594.65..14940.87 rows=10442 width=93) (actual time=108.741..226.150 rows=27792 loops=1)

44. 0.006 0.061 ↑ 1.0 1 1
→

Merge Join (cost=2.36..2.38 rows=1 width=13) (actual time=0.058..0.061 rows=1 loops=1)

  • Merge Cond: (scr.cid = cda.cid)
45. 0.012 0.019 ↑ 1.0 1 1
→

Sort (cost=1.16..1.16 rows=1 width=13) (actual time=0.018..0.019 rows=1 loops=1)

  • Sort Key: scr.cid
  • Sort Method: quicksort Memory: 25kB
46. 0.007 0.007 ↑ 1.0 1 1
→

Seq Scan on sys_co_regs scr (cost=0.00..1.15 rows=1 width=13) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: ((region)::text = 'DLR-US-FLD'::text)
47. 0.012 0.036 ↑ 1.2 6 1
→

Sort (cost=1.17..1.19 rows=7 width=4) (actual time=0.035..0.036 rows=6 loops=1)

  • Sort Key: cda.cid
  • Sort Method: quicksort Memory: 25kB
48. 0.024 0.024 ↑ 1.0 7 1
→

Seq Scan on co_def_attr cda (cost=0.00..1.07 rows=7 width=4) (actual time=0.022..0.024 rows=7 loops=1)

49. 38.130 220.672 ↓ 2.7 27792 1
→

Hash Left Join (cost=10592.29..14834.07 rows=10442 width=89) (actual time=108.678..220.672 rows=27792 loops=1)

  • Hash Cond: (((ldm.log_div)::text = (ira.region)::text) AND ((m.code)::text = (ira.item_code)::text))
50. 29.100 154.685 ↓ 2.7 27792 1
→

Hash Join (cost=5156.65..7647.76 rows=10442 width=25) (actual time=80.685..154.685 rows=27792 loops=1)

  • Hash Cond: (ma.master_material_id = m1.id)
51. 6.603 104.090 ↓ 2.7 27792 1
→

Hash Left Join (cost=3455.12..5423.54 rows=10442 width=29) (actual time=59.027..104.090 rows=27792 loops=1)

  • Hash Cond: (ma.similar_material_id = m2.id)
52. 31.522 78.404 ↓ 2.7 27792 1
→

Hash Join (cost=1753.58..3283.73 rows=10442 width=33) (actual time=39.895..78.404 rows=27792 loops=1)

  • Hash Cond: (m.id = ma.material_id)
53. 7.023 7.023 ↑ 1.0 53446 1
→

Seq Scan on material m (cost=0.00..824.46 rows=53446 width=13) (actual time=0.004..7.023 rows=53446 loops=1)

54. 9.978 39.859 ↓ 2.7 27792 1
→

Hash (cost=1623.06..1623.06 rows=10442 width=24) (actual time=39.859..39.859 rows=27792 loops=1)

55. 13.073 29.881 ↓ 2.7 27792 1
→

Nested Loop (cost=200.67..1623.06 rows=10442 width=24) (actual time=3.796..29.881 rows=27792 loops=1)

56. 0.012 0.012 ↑ 1.0 1 1
→

Seq Scan on log_div_mast ldm (cost=0.00..1.18 rows=1 width=13) (actual time=0.007..0.012 rows=1 loops=1)

  • Filter: ((log_div)::text = 'DLR-US-FLD'::text)
57. 13.158 16.796 ↓ 3.0 27792 1
→

Bitmap Heap Scan on material_attr ma (cost=200.67..1505.10 rows=9343 width=19) (actual time=3.780..16.796 rows=27792 loops=1)

  • Recheck Cond: (ma.region_id = ldm.material_master_region_id)
58. 3.638 3.638 ↓ 3.0 27792 1
→

Bitmap Index Scan on idx_material_attr_rid_cc (cost=0.00..198.33 rows=9343 width=0) (actual time=3.638..3.638 rows=27792 loops=1)

  • Index Cond: (ma.region_id = ldm.material_master_region_id)
59. 11.380 19.083 ↑ 1.0 53446 1
→

Hash (cost=824.46..824.46 rows=53446 width=4) (actual time=19.083..19.083 rows=53446 loops=1)

60. 7.703 7.703 ↑ 1.0 53446 1
→

Seq Scan on material m2 (cost=0.00..824.46 rows=53446 width=4) (actual time=0.007..7.703 rows=53446 loops=1)

61. 11.895 21.495 ↑ 1.0 53446 1
→

Hash (cost=824.46..824.46 rows=53446 width=4) (actual time=21.495..21.495 rows=53446 loops=1)

62. 9.600 9.600 ↑ 1.0 53446 1
→

Seq Scan on material m1 (cost=0.00..824.46 rows=53446 width=4) (actual time=0.016..9.600 rows=53446 loops=1)

63. 13.784 27.857 ↓ 1.0 27792 1
→

Hash (cost=4643.44..4643.44 rows=27613 width=82) (actual time=27.857..27.857 rows=27792 loops=1)

64. 10.084 14.073 ↓ 1.0 27792 1
→

Bitmap Heap Scan on item_region_attr ira (cost=750.28..4643.44 rows=27613 width=82) (actual time=4.104..14.073 rows=27792 loops=1)

  • Recheck Cond: ((region)::text = 'DLR-US-FLD'::text)
65. 3.989 3.989 ↓ 1.0 27792 1
→

Bitmap Index Scan on item_region_attr_pkey (cost=0.00..743.38 rows=27613 width=0) (actual time=3.989..3.989 rows=27792 loops=1)

  • Index Cond: ((region)::text = 'DLR-US-FLD'::text)
66. 0.020 0.021 ↓ 0.0 0 1
→

Sort (cost=41.16..42.56 rows=560 width=8) (actual time=0.021..0.021 rows=0 loops=1)

  • Sort Key: cma.cid
  • Sort Method: quicksort Memory: 25kB
67. 0.001 0.001 ↓ 0.0 0 1
→

Seq Scan on co_mc_attr cma (cost=0.00..15.60 rows=560 width=8) (actual time=0.001..0.001 rows=0 loops=1)

68. 0.007 0.036 ↑ 1.0 13 1
→

Hash (cost=2.49..2.49 rows=13 width=15) (actual time=0.036..0.036 rows=13 loops=1)

69. 0.029 0.029 ↑ 1.0 13 1
→

Seq Scan on item_group_mast igm (cost=0.00..2.49 rows=13 width=15) (actual time=0.018..0.029 rows=13 loops=1)

  • Filter: ((region)::text = 'DLR-US-FLD'::text)
70. 0.000 0.035 ↓ 0.0 0 1
→

Hash (cost=4.41..4.41 rows=1 width=16) (actual time=0.035..0.035 rows=0 loops=1)

71. 0.035 0.035 ↓ 0.0 0 1
→

Seq Scan on ld_cc_master lcm (cost=0.00..4.41 rows=1 width=16) (actual time=0.035..0.035 rows=0 loops=1)

  • Filter: ((log_div)::text = 'DLR-US-FLD'::text)
72. 0.013 0.043 ↑ 1.0 45 1
→

Hash (cost=1.45..1.45 rows=45 width=15) (actual time=0.043..0.043 rows=45 loops=1)

73. 0.030 0.030 ↑ 1.0 45 1
→

Seq Scan on pc_mast field_pc (cost=0.00..1.45 rows=45 width=15) (actual time=0.024..0.030 rows=45 loops=1)

74. 0.014 0.021 ↑ 1.0 45 1
→

Hash (cost=1.45..1.45 rows=45 width=15) (actual time=0.021..0.021 rows=45 loops=1)

75. 0.007 0.007 ↑ 1.0 45 1
→

Seq Scan on pc_mast supply_pc (cost=0.00..1.45 rows=45 width=15) (actual time=0.002..0.007 rows=45 loops=1)

76. 0.014 0.021 ↑ 1.0 45 1
→

Hash (cost=1.45..1.45 rows=45 width=15) (actual time=0.021..0.021 rows=45 loops=1)

77. 0.007 0.007 ↑ 1.0 45 1
→

Seq Scan on pc_mast dc_pc (cost=0.00..1.45 rows=45 width=15) (actual time=0.002..0.007 rows=45 loops=1)

78. 18.255 24.557 ↑ 1.0 10147 1
→

Sort (cost=914.80..940.16 rows=10147 width=18) (actual time=22.969..24.557 rows=10147 loops=1)

  • Sort Key: lma.mat_code
  • Sort Method: external sort Disk: 336kB
79. 6.302 6.302 ↑ 1.0 10147 1
→

Seq Scan on ld_mc_attr lma (cost=0.00..239.58 rows=10147 width=18) (actual time=0.030..6.302 rows=10147 loops=1)

  • Filter: ((log_div)::text = 'DLR-US-FLD'::text) Total runtime: 3327.411 ms