explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9JKa

Settings
# exclusive inclusive rows x rows loops node
1. 0.578 977.713 ↑ 1,597.4 41 1

HashAggregate (cost=583,561.58..584,543.97 rows=65,493 width=2,131) (actual time=977.215..977.713 rows=41 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, (string_agg(((st1.locale_id)::character varying)::text, ','::text ORDER BY st1.locale_id)), (min(CASE WHEN ((vis_translation_custom_object.field)::text = 'title'::text) THEN v2.translation ELSE NULL::text END)), (min(CASE WHEN ((vis_translation_custom_object.field)::text = 'description'::text) THEN v2.translation ELSE NULL::text END))
2.          

CTE ns

3. 0.292 936.279 ↑ 6.9 158 1

HashAggregate (cost=502,748.07..502,758.93 rows=1,086 width=1,888) (actual time=936.224..936.279 rows=158 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.created_by, named_selections_summ_tbl.created, named_selections_summ_tbl.last_upd, named_selections_summ_tbl.last_upd_by, named_selections_summ_tbl.is_static_object, named_selections_summ_tbl.is_drillable
4. 0.000 935.987 ↑ 6.8 159 1

Append (cost=1,000.00..502,712.78 rows=1,086 width=1,888) (actual time=21.234..935.987 rows=159 loops=1)

5. 17.064 195.373 ↓ 2.5 40 1

Gather (cost=1,000.00..180,947.65 rows=16 width=144) (actual time=21.233..195.373 rows=40 loops=1)

  • Workers Planned: 12
  • Workers Launched: 12
6. 178.309 178.309 ↓ 3.0 3 13 / 13

Parallel Seq Scan on named_selections_summ_tbl (cost=0.00..179,946.05 rows=1 width=144) (actual time=102.258..178.309 rows=3 loops=13)

  • Filter: ((object_type = '7'::numeric) AND ((org_name)::text = 'IRI Solutions'::text) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 108,508
7. 20.667 203.350 ↑ 11.1 85 1

Gather (cost=1,000.00..180,893.81 rows=947 width=144) (actual time=60.713..203.350 rows=85 loops=1)

  • Workers Planned: 12
  • Workers Launched: 12
8. 182.683 182.683 ↑ 11.3 7 13 / 13

Parallel Seq Scan on named_selections_summ_tbl named_selections_summ_tbl_1 (cost=0.00..179,799.11 rows=79 width=144) (actual time=35.485..182.683 rows=7 loops=13)

  • Filter: ((object_type = '7'::numeric) AND ((org_name)::text = 'IRI Solutions'::text) AND (scope = ANY ('{1,3,5}'::numeric[])))
  • Rows Removed by Filter: 108,504
9. 14.428 537.628 ↑ 3.6 34 1

Hash Join (cost=118,850.63..140,855.02 rows=123 width=144) (actual time=489.701..537.628 rows=34 loops=1)

  • Hash Cond: ((obj_user_map_1.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
10. 122.721 312.533 ↑ 6.5 149,251 1

HashAggregate (cost=56,917.98..66,643.67 rows=972,569 width=118) (actual time=277.885..312.533 rows=149,251 loops=1)

  • Group Key: obj_user_map_1.object_id
11. 12.866 189.812 ↑ 4.5 217,452 1

Append (cost=64.98..54,486.55 rows=972,569 width=118) (actual time=0.069..189.812 rows=217,452 loops=1)

12. 0.441 0.490 ↑ 19.1 302 1

Bitmap Heap Scan on obj_user_map obj_user_map_1 (cost=64.98..5,320.01 rows=5,774 width=45) (actual time=0.068..0.490 rows=302 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=164
13. 0.049 0.049 ↑ 19.0 304 1

Bitmap Index Scan on obj_user_map_user_id_lower (cost=0.00..63.53 rows=5,774 width=0) (actual time=0.049..0.049 rows=304 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
14. 26.927 176.456 ↑ 4.5 217,150 1

Nested Loop (cost=582.53..34,578.01 rows=966,795 width=44) (actual time=0.067..176.456 rows=217,150 loops=1)

15. 0.026 0.068 ↑ 46.5 13 1

HashAggregate (cost=582.11..588.15 rows=604 width=8) (actual time=0.050..0.068 rows=13 loops=1)

  • Group Key: adm_grp_usr_map_1.group_id
16. 0.025 0.042 ↑ 54.1 13 1

Bitmap Heap Scan on adm_grp_usr_map adm_grp_usr_map_1 (cost=9.17..580.35 rows=703 width=8) (actual time=0.021..0.042 rows=13 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
17. 0.017 0.017 ↑ 54.1 13 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..8.99 rows=703 width=0) (actual time=0.017..0.017 rows=13 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
18. 149.461 149.461 ↓ 21.2 16,704 13

Index Scan using idx705 on obj_usergrp_map obj_usergrp_map_1 (cost=0.43..48.38 rows=789 width=52) (actual time=0.013..11.497 rows=16,704 loops=13)

  • Index Cond: (group_id = adm_grp_usr_map_1.group_id)
19. 0.073 210.667 ↑ 1.4 132 1

Hash (cost=61,930.42..61,930.42 rows=179 width=144) (actual time=210.667..210.667 rows=132 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
20. 210.594 210.594 ↑ 1.4 132 1

Index Scan using named_selections_summ_tbl_ind on named_selections_summ_tbl named_selections_summ_tbl_2 (cost=0.43..61,930.42 rows=179 width=144) (actual time=119.951..210.594 rows=132 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (scope = '2'::numeric))
  • Filter: ((org_name)::text = 'IRI Solutions'::text)
  • Rows Removed by Filter: 41,451
21. 0.044 977.135 ↑ 1,597.4 41 1

Hash Right Join (cost=71,519.14..77,036.80 rows=65,493 width=2,131) (actual time=976.101..977.135 rows=41 loops=1)

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (ns.object_id)::text)
22. 1.045 3.796 ↑ 3,927.1 33 1

HashAggregate (cost=67,859.62..69,155.55 rows=129,593 width=76) (actual time=2.793..3.796 rows=33 loops=1)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
23. 0.029 2.751 ↑ 14,648.4 33 1

WindowAgg (cost=56,983.17..65,442.63 rows=483,398 width=76) (actual time=2.729..2.751 rows=33 loops=1)

24. 0.079 2.722 ↑ 14,648.4 33 1

Sort (cost=56,983.17..58,191.66 rows=483,398 width=46) (actual time=2.720..2.722 rows=33 loops=1)

  • Sort Key: obj_usergrp_map.object_id
  • Sort Method: quicksort Memory: 27kB
25. 0.024 2.643 ↑ 14,648.4 33 1

Hash Join (cost=620.68..11,343.50 rows=483,398 width=46) (actual time=0.197..2.643 rows=33 loops=1)

  • Hash Cond: (obj_usergrp_map.group_id = adm_grp_usr_map.group_id)
26. 0.086 2.556 ↑ 19,635.3 33 1

Nested Loop (cost=24.99..3,669.10 rows=647,965 width=54) (actual time=0.116..2.556 rows=33 loops=1)

27. 0.073 0.100 ↑ 1.3 158 1

HashAggregate (cost=24.43..26.43 rows=200 width=118) (actual time=0.079..0.100 rows=158 loops=1)

  • Group Key: (ns_1.object_id)::text
28. 0.027 0.027 ↑ 6.9 158 1

CTE Scan on ns ns_1 (cost=0.00..21.72 rows=1,086 width=118) (actual time=0.002..0.027 rows=158 loops=1)

29. 2.370 2.370 ↓ 0.0 0 158

Index Scan using idx706 on obj_usergrp_map (cost=0.55..18.05 rows=16 width=54) (actual time=0.015..0.015 rows=0 loops=158)

  • Index Cond: ((object_id)::text = (ns_1.object_id)::text)
30. 0.005 0.063 ↑ 46.5 13 1

Hash (cost=588.15..588.15 rows=604 width=8) (actual time=0.062..0.063 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.011 0.058 ↑ 46.5 13 1

HashAggregate (cost=582.11..588.15 rows=604 width=8) (actual time=0.053..0.058 rows=13 loops=1)

  • Group Key: adm_grp_usr_map.group_id
32. 0.027 0.047 ↑ 54.1 13 1

Bitmap Heap Scan on adm_grp_usr_map (cost=9.17..580.35 rows=703 width=8) (actual time=0.026..0.047 rows=13 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
33. 0.020 0.020 ↑ 54.1 13 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..8.99 rows=703 width=0) (actual time=0.020..0.020 rows=13 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
34. 0.043 973.295 ↑ 2.5 41 1

Hash (cost=3,658.25..3,658.25 rows=101 width=2,099) (actual time=973.295..973.295 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
35. 0.049 973.252 ↑ 2.5 41 1

Hash Left Join (cost=1,825.06..3,658.25 rows=101 width=2,099) (actual time=947.909..973.252 rows=41 loops=1)

  • Hash Cond: ((ns.object_id)::text = (oum.object_id)::text)
36. 0.017 970.551 ↓ 5.9 41 1

Nested Loop Left Join (cost=905.17..2,734.81 rows=7 width=2,067) (actual time=945.229..970.551 rows=41 loops=1)

37. 8.498 969.919 ↓ 5.9 41 1

Nested Loop (cost=904.75..2,725.73 rows=7 width=2,061) (actual time=945.199..969.919 rows=41 loops=1)

  • Join Filter: (ns.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 55,678
38. 0.017 945.103 ↓ 41.0 41 1

Nested Loop Left Join (cost=904.75..1,812.75 rows=1 width=2,038) (actual time=943.722..945.103 rows=41 loops=1)

39. 0.006 944.635 ↓ 41.0 41 1

Nested Loop Left Join (cost=904.33..1,811.37 rows=1 width=2,031) (actual time=943.700..944.635 rows=41 loops=1)

40. 0.021 943.768 ↓ 41.0 41 1

Nested Loop Left Join (cost=903.78..1,809.73 rows=1 width=1,988) (actual time=943.658..943.768 rows=41 loops=1)

  • Join Filter: ((ns.object_id)::text = (vis_translation_custom_object.object_id)::text)
41. 0.030 943.706 ↓ 41.0 41 1

Nested Loop Left Join (cost=887.69..1,791.31 rows=1 width=1,924) (actual time=943.632..943.706 rows=41 loops=1)

  • Join Filter: ((ns.object_id)::text = (st1.object_id)::text)
42. 4.255 943.635 ↓ 41.0 41 1

Hash Right Join (cost=887.54..1,748.46 rows=1 width=1,892) (actual time=943.613..943.635 rows=41 loops=1)

  • Hash Cond: ((tm.object_id)::text = (ns.object_id)::text)
43. 2.662 2.662 ↑ 1.0 35,630 1

Seq Scan on tag_master tm (cost=0.00..727.30 rows=35,630 width=47) (actual time=0.008..2.662 rows=35,630 loops=1)

44. 0.021 936.718 ↓ 41.0 41 1

Hash (cost=887.52..887.52 rows=1 width=1,888) (actual time=936.718..936.718 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
45. 0.057 936.697 ↓ 41.0 41 1

Hash Join (cost=862.95..887.52 rows=1 width=1,888) (actual time=936.547..936.697 rows=41 loops=1)

  • Hash Cond: ((ns.object_id)::text = (fo.object_id)::text)
46. 936.354 936.354 ↑ 6.9 158 1

CTE Scan on ns (cost=0.00..21.72 rows=1,086 width=1,888) (actual time=936.228..936.354 rows=158 loops=1)

47. 0.043 0.286 ↑ 10.0 125 1

Hash (cost=847.35..847.35 rows=1,248 width=44) (actual time=0.286..0.286 rows=125 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 26kB
48. 0.243 0.243 ↑ 10.0 125 1

Index Scan using idx707 on folder_obj_map fo (cost=0.43..847.35 rows=1,248 width=44) (actual time=0.031..0.243 rows=125 loops=1)

  • Index Cond: (folder_id = ANY ('{1383957,1389969,1394449,988106,10,1394649,1394650,1256579,1394629,1394630,1383937,1004314,963609,971475,1394379,1394378,1029998,698281,1006380,1001515,1394611,1394429}'::bigint[]))
49. 0.000 0.041 ↓ 0.0 0 41

GroupAggregate (cost=0.15..38.35 rows=200 width=76) (actual time=0.001..0.001 rows=0 loops=41)

  • Group Key: st1.object_id
50. 0.000 0.041 ↓ 0.0 0 41

Unique (cost=0.15..24.05 rows=590 width=46) (actual time=0.001..0.001 rows=0 loops=41)

51. 0.041 0.041 ↓ 0.0 0 41

Index Only Scan using vis_trans_cust_obj_idx1 on vis_translation_custom_object st1 (cost=0.15..21.10 rows=590 width=46) (actual time=0.001..0.001 rows=0 loops=41)

  • Heap Fetches: 0
52. 0.000 0.041 ↓ 0.0 0 41

GroupAggregate (cost=16.09..18.40 rows=1 width=108) (actual time=0.001..0.001 rows=0 loops=41)

  • Group Key: vis_translation_custom_object.object_id
53. 0.000 0.041 ↓ 0.0 0 41

Nested Loop Left Join (cost=16.09..18.38 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=41)

54. 0.000 0.041 ↓ 0.0 0 41

GroupAggregate (cost=15.94..15.98 rows=1 width=57) (actual time=0.001..0.001 rows=0 loops=41)

  • Group Key: vis_translation_custom_object.object_id, vis_translation_custom_object.field
55. 0.033 0.041 ↓ 0.0 0 41

Sort (cost=15.94..15.95 rows=1 width=55) (actual time=0.001..0.001 rows=0 loops=41)

  • Sort Key: vis_translation_custom_object.object_id, vis_translation_custom_object.field
  • Sort Method: quicksort Memory: 25kB
56. 0.008 0.008 ↓ 0.0 0 1

Index Scan using vis_trans_cust_obj_idx1 on vis_translation_custom_object (cost=0.15..15.93 rows=1 width=55) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (locale_id = ANY ('{2,1,0}'::integer[]))
57. 0.000 0.000 ↓ 0.0 0

Index Scan using vis_trans_cust_obj_idx1 on vis_translation_custom_object v2 (cost=0.15..2.37 rows=1 width=70) (never executed)

  • Index Cond: (((object_id)::text = (vis_translation_custom_object.object_id)::text) AND (locale_id = (CASE min(CASE vis_translation_custom_object.locale_id WHEN 2 THEN 0 WHEN 1 THEN 1 WHEN 0 THEN 2 ELSE NULL::integer END) WHEN 0 THEN 2 WHEN 1 THEN 1 WHEN 2 THEN 0 ELSE NULL::integer END)))
  • Filter: ((field)::text = (vis_translation_custom_object.field)::text)
58. 0.861 0.861 ↑ 1.0 1 41

Index Only Scan using "idx$$_6dbe60003" on obj_filter_map_tbl ofmt (cost=0.55..1.63 rows=1 width=87) (actual time=0.020..0.021 rows=1 loops=41)

  • Index Cond: (object_id = (ns.object_id)::text)
  • Heap Fetches: 0
59. 0.451 0.451 ↓ 0.0 0 41

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl ft (cost=0.41..1.37 rows=1 width=45) (actual time=0.011..0.011 rows=0 loops=41)

  • Index Cond: (((ns.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
60. 16.318 16.318 ↑ 1.0 1,359 41

Seq Scan on cube_tbl cb (cost=0.00..892.59 rows=1,359 width=31) (actual time=0.001..0.398 rows=1,359 loops=41)

61. 0.615 0.615 ↑ 1.0 1 41

Index Only Scan using inx_objcollmap_objid_collid on object_collection_map objcolmap (cost=0.42..1.29 rows=1 width=48) (actual time=0.014..0.015 rows=1 loops=41)

  • Index Cond: (obj_id = (ns.object_id)::text)
  • Heap Fetches: 0
62. 0.003 2.652 ↑ 2,887.0 1 1

Hash (cost=883.80..883.80 rows=2,887 width=77) (actual time=2.652..2.652 rows=1 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 33kB
63. 0.002 2.649 ↑ 2,887.0 1 1

Subquery Scan on oum (cost=826.06..883.80 rows=2,887 width=77) (actual time=2.641..2.649 rows=1 loops=1)

64. 0.022 2.647 ↑ 2,887.0 1 1

HashAggregate (cost=826.06..854.93 rows=2,887 width=295) (actual time=2.640..2.647 rows=1 loops=1)

  • Group Key: max(obj_user_map.is_editable) OVER (?), obj_user_map.object_id, obj_user_map.user_id
65. 0.009 2.625 ↑ 2,887.0 1 1

WindowAgg (cost=746.67..804.41 rows=2,887 width=295) (actual time=2.624..2.625 rows=1 loops=1)

66. 0.010 2.616 ↑ 2,887.0 1 1

Sort (cost=746.67..753.89 rows=2,887 width=265) (actual time=2.616..2.616 rows=1 loops=1)

  • Sort Key: obj_user_map.user_id, obj_user_map.object_id
  • Sort Method: quicksort Memory: 25kB
67. 0.000 2.606 ↑ 2,887.0 1 1

Nested Loop (cost=24.99..580.73 rows=2,887 width=265) (actual time=2.278..2.606 rows=1 loops=1)

68. 0.067 0.093 ↑ 1.3 158 1

HashAggregate (cost=24.43..26.43 rows=200 width=118) (actual time=0.073..0.093 rows=158 loops=1)

  • Group Key: (ns_2.object_id)::text
69. 0.026 0.026 ↑ 6.9 158 1

CTE Scan on ns ns_2 (cost=0.00..21.72 rows=1,086 width=118) (actual time=0.002..0.026 rows=158 loops=1)

70. 2.528 2.528 ↓ 0.0 0 158

Index Scan using obj_user_map_objid_usrid_i on obj_user_map (cost=0.55..2.76 rows=1 width=265) (actual time=0.016..0.016 rows=0 loops=158)

  • Index Cond: (((object_id)::text = (ns_2.object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
Planning time : 8.481 ms
Execution time : 1,007.042 ms