explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nhxb

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 1,904.652 ↑ 7.7 3 1

Sort (cost=5,895.47..5,895.53 rows=23 width=238) (actual time=1,904.649..1,904.652 rows=3 loops=1)

  • Sort Key: ao.codetext, a.start_date
  • Sort Method: quicksort Memory: 25kB
2. 1.140 1,904.597 ↑ 7.7 3 1

Nested Loop (cost=5,612.51..5,894.95 rows=23 width=238) (actual time=1,230.232..1,904.597 rows=3 loops=1)

  • Join Filter: ((a.owner)::text = ('1'::text))
  • Rows Removed by Join Filter: 370
3. 32.144 1,888.417 ↓ 188.0 188 1

Nested Loop Left Join (cost=4,722.39..4,857.35 rows=1 width=344) (actual time=187.479..1,888.417 rows=188 loops=1)

  • Filter: ((org_user_cl.active = '1'::numeric) OR (pr.org IS NULL) OR (((ou.active = '1'::numeric) OR (pr.org IS NULL)) AND (hashed SubPlan 1)))
  • Rows Removed by Filter: 151
4. 9.098 1,744.584 ↓ 339.0 339 1

Nested Loop Left Join (cost=3,989.64..4,099.76 rows=1 width=353) (actual time=187.396..1,744.584 rows=339 loops=1)

5. 1.928 1,732.435 ↓ 339.0 339 1

Nested Loop Left Join (cost=3,988.98..4,094.39 rows=1 width=348) (actual time=187.333..1,732.435 rows=339 loops=1)

6. 3.082 1,728.134 ↓ 339.0 339 1

Nested Loop (cost=3,988.70..4,093.76 rows=1 width=335) (actual time=187.303..1,728.134 rows=339 loops=1)

7. 772.658 1,719.967 ↓ 113.0 339 1

Nested Loop (cost=3,988.42..4,092.82 rows=3 width=306) (actual time=187.247..1,719.967 rows=339 loops=1)

  • Join Filter: ((a.owner)::text = ao.codenum)
  • Rows Removed by Join Filter: 686814
8. 1.678 73.367 ↓ 113.0 339 1

Nested Loop Left Join (cost=38.86..125.91 rows=3 width=242) (actual time=2.505..73.367 rows=339 loops=1)

9. 1.597 69.994 ↓ 113.0 339 1

Nested Loop Left Join (cost=38.72..125.35 rows=3 width=227) (actual time=2.494..69.994 rows=339 loops=1)

10. 1.695 67.380 ↓ 113.0 339 1

Nested Loop Left Join (cost=38.57..124.69 rows=3 width=109) (actual time=2.482..67.380 rows=339 loops=1)

11. 11.763 63.990 ↓ 113.0 339 1

Nested Loop (cost=38.43..124.13 rows=3 width=94) (actual time=2.469..63.990 rows=339 loops=1)

12. 11.485 29.433 ↓ 67.8 3,799 1

Nested Loop (cost=38.15..101.28 rows=56 width=49) (actual time=1.810..29.433 rows=3,799 loops=1)

  • Join Filter: ((sub.dp_otype = pl.parent_otype) AND (sub.dp_oid = pl.parent_oid))
13. 2.522 6.770 ↓ 12.2 414 1

Merge Join (cost=37.87..87.60 rows=34 width=49) (actual time=1.778..6.770 rows=414 loops=1)

  • Merge Cond: ((pl2.dp_otype = sub.dp_otype) AND (pl2.dp_oid = sub.dp_oid))
14. 1.761 1.761 ↑ 13.0 415 1

Index Scan using plnk_unique_cid_idx on plnk pl2 (cost=0.28..586.19 rows=5,410 width=16) (actual time=0.029..1.761 rows=415 loops=1)

15. 1.624 2.487 ↑ 1.1 414 1

Sort (cost=37.58..38.71 rows=452 width=33) (actual time=1.736..2.487 rows=414 loops=1)

  • Sort Key: sub.dp_otype, sub.dp_oid
  • Sort Method: quicksort Memory: 57kB
16. 0.863 0.863 ↑ 1.1 414 1

Seq Scan on subproject sub (cost=0.00..17.65 rows=452 width=33) (actual time=0.016..0.863 rows=414 loops=1)

  • Filter: ((app_name)::text ~~ 'fpp'::text)
17. 11.178 11.178 ↓ 9.0 9 414

Index Scan using plnk_idx on plnk pl (cost=0.28..0.39 rows=1 width=16) (actual time=0.008..0.027 rows=9 loops=414)

  • Index Cond: ((parent_oid = pl2.dp_oid) AND (parent_otype = pl2.dp_otype))
18. 22.794 22.794 ↓ 0.0 0 3,799

Index Scan using activity_oid_idx on activity a (cost=0.28..0.40 rows=1 width=53) (actual time=0.006..0.006 rows=0 loops=3,799)

  • Index Cond: (dp_oid = pl.dp_oid)
  • Filter: ((end_date IS NULL) AND (start_date IS NOT NULL) AND (dp_state = 0) AND (pl.dp_otype = dp_otype) AND ((dp_otype = 2506) OR (dp_otype = 2507) OR (dp_otype = 2580)))
  • Rows Removed by Filter: 1
19. 1.695 1.695 ↑ 1.0 1 339

Index Scan using activity_type_cl_pkey on activity_type_cl at (cost=0.14..0.19 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=339)

  • Index Cond: (a.activity_type = codenum)
20. 1.017 1.017 ↓ 0.0 0 339

Index Scan using milestone_type_cl_pkey on milestone_type_cl mt (cost=0.14..0.22 rows=1 width=122) (actual time=0.003..0.003 rows=0 loops=339)

  • Index Cond: (a.activity_type = codenum)
21. 1.695 1.695 ↑ 1.0 1 339

Index Scan using dec_point_type_cl_pkey on dec_point_type_cl dpt (cost=0.14..0.18 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=339)

  • Index Cond: (a.activity_type = codenum)
22. 684.990 873.942 ↓ 7.9 2,027 339

Materialize (cost=3,949.56..3,955.99 rows=257 width=64) (actual time=0.539..2.578 rows=2,027 loops=339)

23. 4.135 188.952 ↓ 7.9 2,027 1

Subquery Scan on ao (cost=3,949.56..3,954.70 rows=257 width=64) (actual time=182.237..188.952 rows=2,027 loops=1)

24. 7.452 184.817 ↓ 7.9 2,027 1

HashAggregate (cost=3,949.56..3,952.13 rows=257 width=64) (actual time=182.234..184.817 rows=2,027 loops=1)

  • Group Key: b.codenum, b.codetext
25. 5.398 177.365 ↑ 1.0 2,512 1

Subquery Scan on b (cost=3,878.92..3,911.03 rows=2,569 width=418) (actual time=169.219..177.365 rows=2,512 loops=1)

26. 31.768 171.967 ↑ 1.0 2,512 1

Sort (cost=3,878.92..3,885.34 rows=2,569 width=450) (actual time=169.209..171.967 rows=2,512 loops=1)

  • Sort Key: a_1.sorttext, a_1.dp_oid, a_1.num, a_1.codetext
  • Sort Method: quicksort Memory: 446kB
27. 5.301 140.199 ↑ 1.0 2,512 1

Subquery Scan on a_1 (cost=3,682.04..3,733.42 rows=2,569 width=450) (actual time=130.247..140.199 rows=2,512 loops=1)

28. 20.801 134.898 ↑ 1.0 2,512 1

HashAggregate (cost=3,682.04..3,707.73 rows=2,569 width=450) (actual time=130.244..134.898 rows=2,512 loops=1)

  • Group Key: "*SELECT* 1".dp_oid, "*SELECT* 1".sorttext, "*SELECT* 1".codenum, "*SELECT* 1".codetext, (0), (NULL::character varying), (NULL::character varying), (NULL::character varying), "*SELECT* 1".email, (NULL::character varying), (NULL::integer), (NULL::integer), ((1)::numeric)
29. 4.907 114.097 ↑ 1.0 2,515 1

Append (cost=0.00..3,598.55 rows=2,569 width=450) (actual time=0.050..114.097 rows=2,515 loops=1)

30. 0.224 0.734 ↑ 1.0 88 1

Subquery Scan on *SELECT* 1 (cost=0.00..25.98 rows=88 width=420) (actual time=0.047..0.734 rows=88 loops=1)

31. 0.510 0.510 ↑ 1.0 88 1

Seq Scan on activity_inbox i (cost=0.00..24.88 rows=88 width=392) (actual time=0.043..0.510 rows=88 loops=1)

32. 1.212 53.990 ↑ 1.0 504 1

Subquery Scan on *SELECT* 2 (cost=1,712.00..1,753.14 rows=528 width=150) (actual time=49.178..53.990 rows=504 loops=1)

33. 1.560 52.778 ↑ 1.0 504 1

Hash Join (cost=1,712.00..1,747.86 rows=528 width=127) (actual time=49.174..52.778 rows=504 loops=1)

  • Hash Cond: ((m.member)::text = (ui_1.codenum)::text)
34. 1.445 2.382 ↑ 1.0 525 1

Hash Join (cost=4.20..31.48 rows=529 width=34) (actual time=0.296..2.382 rows=525 loops=1)

  • Hash Cond: ((m.dp_otype = i_1.dp_otype) AND (m.dp_oid = i_1.dp_oid))
35. 0.682 0.682 ↑ 1.3 525 1

Seq Scan on inbox_member m (cost=0.00..16.85 rows=685 width=17) (actual time=0.019..0.682 rows=525 loops=1)

36. 0.123 0.255 ↑ 1.0 88 1

Hash (cost=2.88..2.88 rows=88 width=25) (actual time=0.255..0.255 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
37. 0.132 0.132 ↑ 1.0 88 1

Seq Scan on activity_inbox i_1 (cost=0.00..2.88 rows=88 width=25) (actual time=0.011..0.132 rows=88 loops=1)

38. 3.596 48.836 ↑ 1.0 1,864 1

Hash (cost=1,684.14..1,684.14 rows=1,893 width=100) (actual time=48.836..48.836 rows=1,864 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 262kB
39. 4.386 45.240 ↑ 1.0 1,864 1

Subquery Scan on ui_1 (cost=1,660.47..1,684.14 rows=1,893 width=100) (actual time=38.856..45.240 rows=1,864 loops=1)

40. 15.249 40.854 ↑ 1.0 1,864 1

Sort (cost=1,660.47..1,665.21 rows=1,893 width=388) (actual time=38.850..40.854 rows=1,864 loops=1)

  • Sort Key: (btrim(((((((nvl2(ui_2.first_name, ((btrim((ui_2.first_name)::text) || ' '::text))::character varying, ''::character varying))::text || (nvl2(ui_2.middle_name, ((btrim((ui_2.middle_name)::text) || ' '::text))::character varying, ''::character varying))::text) || (nvl2(ui_2.last_name, (btrim((ui_2.last_name)::text))::character varying, ''::character varying))::text) || ' ('::text) || (ui_2.user_name)::text) || ')'::text)))
  • Sort Method: quicksort Memory: 419kB
41. 25.605 25.605 ↑ 1.0 1,864 1

Seq Scan on user_info ui_2 (cost=0.00..1,557.43 rows=1,893 width=388) (actual time=0.052..25.605 rows=1,864 loops=1)

  • Filter: (active = '1'::numeric)
  • Rows Removed by Filter: 75
42. 4.244 54.466 ↑ 1.0 1,923 1

Subquery Scan on *SELECT* 3 (cost=1,745.59..1,819.43 rows=1,953 width=165) (actual time=42.979..54.466 rows=1,923 loops=1)

43. 5.205 50.222 ↑ 1.0 1,923 1

Hash Anti Join (cost=1,745.59..1,799.90 rows=1,953 width=144) (actual time=42.974..50.222 rows=1,923 loops=1)

  • Hash Cond: ((ui_3.user_name)::text = (im.member)::text)
  • Join Filter: (ui_3.active = '1'::numeric)
44. 16.007 43.527 ↑ 1.0 1,939 1

Sort (cost=1,720.17..1,725.10 rows=1,970 width=388) (actual time=41.452..43.527 rows=1,939 loops=1)

  • Sort Key: (btrim(((((((nvl2(ui_3.first_name, ((btrim((ui_3.first_name)::text) || ' '::text))::character v
  • Sort Method: quicksort Memory: 435kB
45. 27.520 27.520 ↑ 1.0 1,939 1

Seq Scan on user_info ui_3 (cost=0.00..1,612.37 rows=1,970 width=388) (actual time=0.079..27.520 rows=1,939 loops=1)

46. 0.736 1.490 ↑ 1.3 524 1

Hash (cost=16.85..16.85 rows=685 width=9) (actual time=1.490..1.490 rows=524 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
47. 0.754 0.754 ↑ 1.3 525 1

Seq Scan on inbox_member im (cost=0.00..16.85 rows=685 width=9) (actual time=0.021..0.754 rows=525 loops=1)

48. 5.085 5.085 ↑ 1.0 1 339

Index Scan using project_unique_cid_idx on project pr (cost=0.28..0.31 rows=1 width=37) (actual time=0.013..0.015 rows=1 loops=339)

  • Index Cond: ((dp_otype = pl2.parent_otype) AND (dp_oid = pl2.parent_oid))
49. 2.373 2.373 ↑ 1.0 1 339

Index Scan using dp_subtypes_pkey on dp_subtypes sp_dps (cost=0.28..0.63 rows=1 width=21) (actual time=0.007..0.007 rows=1 loops=339)

  • Index Cond: ((dp_otype = sub.dp_otype) AND (dp_subtype = sub.dp_subtype))
50. 3.051 3.051 ↓ 0.0 0 339

Index Only Scan using org_user_cl_idx on org_user_cl (cost=0.67..5.36 rows=1 width=9) (actual time=0.009..0.009 rows=0 loops=339)

  • Index Cond: ((codenum = pr.org) AND (user_name = (sys_context('DP_TRANS_CTX'::character varying, 'USER'::character varying))::text))
  • Heap Fetches: 7
51. 1.175 14.916 ↓ 0.0 0 339

Nested Loop (cost=1.15..25.97 rows=1 width=9) (actual time=0.043..0.044 rows=0 loops=339)

52. 1.196 13.221 ↓ 0.0 0 339

Nested Loop (cost=0.87..22.69 rows=1 width=24) (actual time=0.038..0.039 rows=0 loops=339)

53. 2.990 11.865 ↓ 0.0 0 339

Nested Loop (cost=0.72..22.33 rows=1 width=78) (actual time=0.035..0.035 rows=0 loops=339)

54. 3.946 8.475 ↓ 0.0 0 339

Hash Join (cost=0.19..13.78 rows=1 width=67) (actual time=0.025..0.025 rows=0 loops=339)

  • Hash Cond: ((eng_type_ou.codetext)::text = (ou.org_id)::text)
55. 0.800 0.800 ↑ 260.0 1 160

Seq Scan on ui_inv_senior_eng_org_units_cl eng_type_ou (cost=0.00..12.60 rows=260 width=276) (actual time=0.004..0.005 rows=1 loops=160)

56. 1.695 3.729 ↓ 0.0 0 339

Hash (cost=0.18..0.18 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=339)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
57. 2.034 2.034 ↓ 0.0 0 339

Index Scan using org_unit_pkey on org_unit ou (cost=0.14..0.18 rows=1 width=16) (actual time=0.005..0.006 rows=0 loops=339)

  • Index Cond: (id = pr.org)
58. 0.400 0.400 ↑ 1.0 1 20

Index Only Scan using user_info_pkey on user_info ui (cost=0.53..8.54 rows=1 width=11) (actual time=0.018..0.020 rows=1 loops=20)

  • Index Cond: (user_name = (sys_context('DP_TRANS_CTX'::character varying, 'USER'::character varying))::text)
  • Heap Fetches: 20
59. 0.160 0.160 ↑ 1.0 1 20

Index Scan using ui_inv_senior_eng_type_cl_senior_eng_type on ui_inv_senior_eng_type_cl eng_type (cost=0.15..0.35 rows=1 width=62) (actual time=0.008..0.008 rows=1 loops=20)

  • Index Cond: ((senior_eng_type)::text = (eng_type_ou.senior_eng_type)::text)
60. 0.520 0.520 ↑ 1.0 1 20

Index Scan using user_info_cst_inv_senior_eng_type on user_info_cst ui_cst (cost=0.28..3.27 rows=1 width=15) (actual time=0.023..0.026 rows=1 loops=20)

  • Index Cond: (inv_senior_eng_type = eng_type.codenum)
  • Filter: ((user_info_user_name)::text = (sys_context('DP_TRANS_CTX'::character varying, 'USER'::character varying))::text)
61.          

SubPlan (for Nested Loop Left Join)

62. 50.952 96.773 ↑ 1.0 14,262 1

HashAggregate (cost=548.60..695.00 rows=14,640 width=186) (actual time=75.266..96.773 rows=14,262 loops=1)

  • Group Key: activites_for_web_label.codenum, activites_for_web_label.label, (2506)
63. 27.324 45.821 ↑ 1.0 14,262 1

Append (cost=0.00..438.80 rows=14,640 width=186) (actual time=0.027..45.821 rows=14,262 loops=1)

64. 7.304 7.304 ↑ 1.0 5,645 1

Seq Scan on activites_for_web_label (cost=0.00..109.45 rows=5,645 width=48) (actual time=0.025..7.304 rows=5,645 loops=1)

65. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on milestones_for_web_label (cost=0.00..13.80 rows=380 width=186) (actual time=0.009..0.009 rows=0 loops=1)

66. 11.184 11.184 ↓ 1.0 8,617 1

Seq Scan on dec_points_for_web_label (cost=0.00..169.15 rows=8,615 width=50) (actual time=0.019..11.184 rows=8,617 loops=1)

67. 10.800 15.040 ↑ 2,261.0 2 188

HashAggregate (cost=890.12..935.34 rows=4,522 width=32) (actual time=0.031..0.080 rows=2 loops=188)

  • Group Key: ('1'::text)
68. 0.010 4.240 ↑ 2,261.0 2 1

Append (cost=0.00..878.82 rows=4,522 width=32) (actual time=0.015..4.240 rows=2 loops=1)

69. 0.014 0.014 ↑ 2,260.0 1 1

Seq Scan on dual (cost=0.00..32.60 rows=2,260 width=32) (actual time=0.012..0.014 rows=1 loops=1)

70. 0.044 0.044 ↑ 2,260.0 1 1

Seq Scan on dual dual_1 (cost=0.00..603.25 rows=2,260 width=32) (actual time=0.042..0.044 rows=1 loops=1)

71. 0.004 4.172 ↓ 0.0 0 1

Hash Join (cost=3.98..197.75 rows=2 width=32) (actual time=4.172..4.172 rows=0 loops=1)

  • Hash Cond: (im_1.dp_oid = ib.dp_oid)
72. 4.168 4.168 ↓ 0.0 0 1

Seq Scan on inbox_member im_1 (cost=0.00..193.24 rows=3 width=4) (actual time=4.168..4.168 rows=0 loops=1)

  • Filter: (upper((member)::text) = upper((sys_context('DP_TRANS_CTX'::character varying, 'USER'::character varying))::text))
  • Rows Removed by Filter: 525
73. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.88..2.88 rows=88 width=4) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Seq Scan on activity_inbox ib (cost=0.00..2.88 rows=88 width=4) (never executed)

Planning time : 21.320 ms
Execution time : 1,907.041 ms