explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vHpD

Settings
# exclusive inclusive rows x rows loops node
1. 11.636 6,261.337 ↑ 1,240.6 356 1

HashAggregate (cost=625,066.12..631,690.79 rows=441,645 width=2,035) (actual time=6,251.473..6,261.337 rows=356 loops=1)

  • Group Key: ns.object_id, ns.title, ns.description, ns.cube_id, ns.dimension_name, ns.object_type, ns.scope, ns.created_by, ns.created, ns.last_upd, ns.last_upd_by, ns.is_static_object, ns.is_drillable, ofmt.filter_name, ft.favourite_id, cb.cube_name, (oum.is_editable)::character varying, ((max(obj_usergrp_map.is_editable) OVER (?)))::character varying, tm.tag_name, objcolmap.collection_id
2. 0.931 6,249.701 ↑ 1,240.6 356 1

Hash Left Join (cost=587,307.55..602,983.87 rows=441,645 width=2,035) (actual time=6,246.363..6,249.701 rows=356 loops=1)

  • Hash Cond: ((ns.object_id)::text = (objcolmap.obj_id)::text)
3. 0.187 6,200.652 ↑ 1,240.0 356 1

Merge Left Join (cost=582,052.25..589,448.48 rows=441,442 width=2,029) (actual time=6,197.418..6,200.652 rows=356 loops=1)

  • Merge Cond: ((ns.object_id)::text = (oum.object_id)::text)
4. 0.243 6,197.768 ↑ 52.3 356 1

Merge Left Join (cost=576,787.61..577,514.26 rows=18,634 width=1,997) (actual time=6,194.705..6,197.768 rows=356 loops=1)

  • Merge Cond: ((ns.object_id)::text = (ft.object_id)::text)
5. 1.518 6,194.323 ↑ 52.3 356 1

Sort (cost=576,787.20..576,833.78 rows=18,634 width=1,990) (actual time=6,194.269..6,194.323 rows=356 loops=1)

  • Sort Key: ns.object_id
  • Sort Method: quicksort Memory: 154kB
6. 0.267 6,192.805 ↑ 52.3 356 1

Hash Left Join (cost=571,459.98..575,465.52 rows=18,634 width=1,990) (actual time=6,184.357..6,192.805 rows=356 loops=1)

  • Hash Cond: ((ns.object_id)::text = (tm.object_id)::text)
7. 15.883 6,183.832 ↑ 52.3 356 1

Hash Right Join (cost=570,514.85..574,074.66 rows=18,634 width=1,986) (actual time=6,175.479..6,183.832 rows=356 loops=1)

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (ns.object_id)::text)
8. 99.260 2,726.603 ↓ 1.3 164,302 1

HashAggregate (cost=152,762.02..154,047.15 rows=128,513 width=76) (actual time=2,698.606..2,726.603 rows=164,302 loops=1)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
9. 129.690 2,627.343 ↑ 3.7 263,382 1

WindowAgg (cost=130,835.79..147,889.52 rows=974,499 width=76) (actual time=2,468.371..2,627.343 rows=263,382 loops=1)

10. 2,307.325 2,497.653 ↑ 3.7 263,382 1

Sort (cost=130,835.79..133,272.04 rows=974,499 width=46) (actual time=2,468.297..2,497.653 rows=263,382 loops=1)

  • Sort Key: obj_usergrp_map.object_id
  • Sort Method: quicksort Memory: 39431kB
11. 36.891 190.328 ↑ 3.7 263,382 1

Nested Loop (cost=540.94..33,900.91 rows=974,499 width=46) (actual time=0.080..190.328 rows=263,382 loops=1)

12. 0.030 0.077 ↑ 35.1 16 1

HashAggregate (cost=540.51..546.12 rows=561 width=8) (actual time=0.057..0.077 rows=16 loops=1)

  • Group Key: adm_grp_usr_map.group_id
13. 0.026 0.047 ↑ 40.8 16 1

Bitmap Heap Scan on adm_grp_usr_map (cost=8.77..538.88 rows=652 width=8) (actual time=0.026..0.047 rows=16 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
14. 0.021 0.021 ↑ 40.8 16 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..8.61 rows=652 width=0) (actual time=0.021..0.021 rows=16 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
15. 153.360 153.360 ↓ 20.0 16,461 16

Index Scan using idx705 on obj_usergrp_map (cost=0.43..51.21 rows=825 width=54) (actual time=0.010..9.585 rows=16,461 loops=16)

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
16. 0.154 3,441.346 ↓ 12.3 356 1

Hash (cost=417,752.47..417,752.47 rows=29 width=1,954) (actual time=3,441.346..3,441.346 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 97kB
17. 50.812 3,441.192 ↓ 12.3 356 1

Nested Loop (cost=416,877.45..417,752.47 rows=29 width=1,954) (actual time=3,368.297..3,441.192 rows=356 loops=1)

  • Join Filter: (ns.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 412248
18. 0.156 3,374.360 ↓ 71.2 356 1

Nested Loop Left Join (cost=416,877.45..416,933.57 rows=5 width=1,931) (actual time=3,367.400..3,374.360 rows=356 loops=1)

19. 0.970 3,368.508 ↓ 71.2 356 1

Merge Join (cost=416,876.90..416,921.46 rows=5 width=1,888) (actual time=3,367.365..3,368.508 rows=356 loops=1)

  • Merge Cond: ((ns.object_id)::text = (fo.object_id)::text)
20. 4.623 3,365.619 ↑ 6.4 970 1

Sort (cost=415,003.15..415,018.70 rows=6,217 width=1,888) (actual time=3,365.467..3,365.619 rows=970 loops=1)

  • Sort Key: ns.object_id
  • Sort Method: quicksort Memory: 283kB
21. 0.084 3,360.996 ↑ 6.4 971 1

Subquery Scan on ns (cost=414,487.08..414,611.42 rows=6,217 width=1,888) (actual time=3,360.279..3,360.996 rows=971 loops=1)

22. 4.308 3,360.912 ↑ 6.4 971 1

HashAggregate (cost=414,487.08..414,549.25 rows=6,217 width=2,352) (actual time=3,360.277..3,360.912 rows=971 loops=1)

  • Group Key: named_selections_summ_tbl.object_id, named_selections_summ_tbl.title, named_selections_summ_tbl.description, named_selections_summ_tbl.cube_id, named_selections_summ_tbl.dimension_name, named_selections_summ_tbl.object_type, named_selections_summ_tbl.scope, named_selections_summ_tbl.object_ref, named_selections_summ_tbl.status, named_selections_summ_tbl.created, named_selections_summ_tbl.created_by, named_selections_summ_tbl.last_upd, named_selections_summ_tbl.last_upd_by, named_selections_summ_tbl.object_definition, named_selections_summ_tbl.object_props, named_selections_summ_tbl.org_name, named_selections_summ_tbl.is_static_object, named_selections_summ_tbl.is_drillable, named_selections_summ_tbl.old_scope, named_selections_summ_tbl.old_created_by, named_selections_summ_tbl.last_accessed
23. 0.000 3,356.604 ↑ 6.4 974 1

Append (cost=1,000.00..414,160.69 rows=6,217 width=2,352) (actual time=243.215..3,356.604 rows=974 loops=1)

24. 75.046 255.426 ↑ 11.1 9 1

Gather (cost=1,000.00..187,722.48 rows=100 width=1,092) (actual time=243.213..255.426 rows=9 loops=1)

  • Workers Planned: 12
  • Workers Launched: 12
25. 180.380 180.380 ↑ 8.0 1 13

Parallel Seq Scan on named_selections_summ_tbl (cost=0.00..186,712.48 rows=8 width=1,092) (actual time=109.970..180.380 rows=1 loops=13)

  • Filter: ((object_type = '7'::numeric) AND ((org_name)::text = 'GSK_LD'::text) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 100766
26. 24.262 204.705 ↑ 6.3 964 1

Gather (cost=1,000.00..188,178.71 rows=6,112 width=1,092) (actual time=2.183..204.705 rows=964 loops=1)

  • Workers Planned: 12
  • Workers Launched: 12
27. 180.443 180.443 ↑ 6.9 74 13

Parallel Seq Scan on named_selections_summ_tbl named_selections_summ_tbl_1 (cost=0.00..186,567.51 rows=509 width=1,092) (actual time=4.335..180.443 rows=74 loops=13)

  • Filter: ((object_type = '7'::numeric) AND ((org_name)::text = 'GSK_LD'::text) AND (scope = ANY ('{1,3,5}'::numeric[])))
  • Rows Removed by Filter: 100693
28. 0.000 2,908.592 ↑ 5.0 1 1

Nested Loop (cost=19,148.47..38,166.24 rows=5 width=1,092) (actual time=2,002.766..2,908.592 rows=1 loops=1)

29. 115.126 458.063 ↓ 21.7 164,435 1

HashAggregate (cost=19,147.92..19,223.85 rows=7,593 width=118) (actual time=421.257..458.063 rows=164,435 loops=1)

  • Group Key: obj_usergrp_map_1.object_id
30. 300.775 342.937 ↓ 34.7 263,682 1

Gather (cost=1,000.00..19,128.94 rows=7,593 width=118) (actual time=1.483..342.937 rows=263,682 loops=1)

  • Workers Planned: 9
  • Workers Launched: 9
31. 0.000 42.162 ↓ 17.8 26,368 10

Parallel Append (cost=0.00..17,369.64 rows=1,480 width=118) (actual time=1.443..42.162 rows=26,368 loops=10)

32. 64.139 77.121 ↓ 19.8 87,794 3

Nested Loop (cost=0.84..1,630.36 rows=4,433 width=44) (actual time=0.104..77.121 rows=87,794 loops=3)

33. 0.071 0.071 ↓ 2.5 5 3

Parallel Index Only Scan using uk_usrgrp on adm_grp_usr_map adm_grp_usr_map_1 (cost=0.42..4.84 rows=2 width=8) (actual time=0.048..0.071 rows=5 loops=3)

  • Index Cond: (user_id = 'zicc232'::text)
  • Heap Fetches: 16
34. 12.911 12.911 ↓ 20.0 16,461 16

Index Scan using idx705 on obj_usergrp_map obj_usergrp_map_1 (cost=0.43..804.51 rows=825 width=52) (actual time=0.023..12.911 rows=16,461 loops=16)

  • Index Cond: (group_id = adm_grp_usr_map_1.group_id)
35. 22.039 22.039 ↓ 6.3 38 8

Parallel Seq Scan on obj_user_map (cost=0.00..15,655.94 rows=6 width=45) (actual time=1.763..22.039 rows=38 loops=8)

  • Filter: ((user_id)::text = 'zicc232'::text)
  • Rows Removed by Filter: 120718
36. 2,466.525 2,466.525 ↓ 0.0 0 164,435

Index Scan using named_selections_summ_tbl_pkey on named_selections_summ_tbl named_selections_summ_tbl_2 (cost=0.55..2.48 rows=1 width=1,092) (actual time=0.015..0.015 rows=0 loops=164,435)

  • Index Cond: ((object_id)::text = (obj_usergrp_map_1.object_id)::text)
  • Filter: ((object_type = '7'::numeric) AND ((org_name)::text = 'GSK_LD'::text) AND (scope = '2'::numeric))
  • Rows Removed by Filter: 0
37. 1.398 1.919 ↑ 7.5 360 1

Sort (cost=1,873.74..1,880.46 rows=2,686 width=44) (actual time=1.888..1.919 rows=360 loops=1)

  • Sort Key: fo.object_id
  • Sort Method: quicksort Memory: 53kB
38. 0.521 0.521 ↑ 7.5 360 1

Index Scan using idx707 on folder_obj_map fo (cost=0.43..1,720.76 rows=2,686 width=44) (actual time=0.026..0.521 rows=360 loops=1)

  • Index Cond: (folder_id = ANY ('{531328,248731,10,276033,64192,563011,86656,424847,1193019,678070,1171957,1171958,1171959,1171960,1171961,1171962,1171963,1171964,1171965,47,1171966,988106,1171967,1256579,501759,275669,275668,698281,267853,1159964,316652,1159962,1159963,1159961,1159954,1159955,316643,1159952,1159953,276382,1159951,1159950,1159949,275633,275632,1161521,267735,504056,64191,58619,1004314,809918,424998}'::bigint[]))
39. 5.696 5.696 ↑ 1.0 1 356

Index Only Scan using "idx$$_6dbe60003" on obj_filter_map_tbl ofmt (cost=0.55..2.41 rows=1 width=87) (actual time=0.016..0.016 rows=1 loops=356)

  • Index Cond: (object_id = (ns.object_id)::text)
  • Heap Fetches: 356
40. 14.772 16.020 ↑ 1.0 1,159 356

Materialize (cost=0.00..720.38 rows=1,159 width=31) (actual time=0.000..0.045 rows=1,159 loops=356)

41. 1.248 1.248 ↑ 1.0 1,159 1

Seq Scan on cube_tbl cb (cost=0.00..714.59 rows=1,159 width=31) (actual time=0.007..1.248 rows=1,159 loops=1)

42. 4.597 8.706 ↑ 1.0 28,761 1

Hash (cost=585.61..585.61 rows=28,761 width=47) (actual time=8.706..8.706 rows=28,761 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2502kB
43. 4.109 4.109 ↑ 1.0 28,761 1

Seq Scan on tag_master tm (cost=0.00..585.61 rows=28,761 width=47) (actual time=0.070..4.109 rows=28,761 loops=1)

44. 3.202 3.202 ↑ 1.0 30 1

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl ft (cost=0.41..633.63 rows=30 width=45) (actual time=0.394..3.202 rows=30 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
45. 0.662 2.697 ↑ 28.0 169 1

Sort (cost=5,264.63..5,276.48 rows=4,739 width=77) (actual time=2.679..2.697 rows=169 loops=1)

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 43kB
46. 0.016 2.035 ↑ 28.0 169 1

Subquery Scan on oum (cost=4,880.53..4,975.31 rows=4,739 width=77) (actual time=1.976..2.035 rows=169 loops=1)

47. 0.333 2.019 ↑ 28.0 169 1

HashAggregate (cost=4,880.53..4,927.92 rows=4,739 width=85) (actual time=1.971..2.019 rows=169 loops=1)

  • Group Key: max(obj_user_map_1.is_editable) OVER (?), obj_user_map_1.object_id, obj_user_map_1.user_id
48. 0.171 1.686 ↑ 16.1 300 1

WindowAgg (cost=4,747.68..4,844.30 rows=4,831 width=85) (actual time=1.534..1.686 rows=300 loops=1)

49. 1.036 1.515 ↑ 16.1 300 1

Sort (cost=4,747.68..4,759.75 rows=4,831 width=55) (actual time=1.503..1.515 rows=300 loops=1)

  • Sort Key: obj_user_map_1.user_id, obj_user_map_1.object_id
  • Sort Method: quicksort Memory: 67kB
50. 0.402 0.479 ↑ 16.1 300 1

Bitmap Heap Scan on obj_user_map obj_user_map_1 (cost=54.37..4,452.07 rows=4,831 width=55) (actual time=0.105..0.479 rows=300 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=159
51. 0.077 0.077 ↑ 15.8 306 1

Bitmap Index Scan on obj_user_map_user_id_lower (cost=0.00..53.16 rows=4,831 width=0) (actual time=0.077..0.077 rows=306 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
52. 27.900 48.118 ↑ 1.0 160,849 1

Hash (cost=3,243.47..3,243.47 rows=160,947 width=48) (actual time=48.118..48.118 rows=160,849 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 14752kB
53. 20.218 20.218 ↑ 1.0 160,849 1

Seq Scan on object_collection_map objcolmap (cost=0.00..3,243.47 rows=160,947 width=48) (actual time=0.016..20.218 rows=160,849 loops=1)

Planning time : 6.901 ms
Execution time : 6,278.936 ms