explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c9nI

Settings
# exclusive inclusive rows x rows loops node
1. 0.159 753.804 ↑ 47.0 356 1

Unique (cost=143,044.95..143,924.17 rows=16,747 width=2,035) (actual time=753.634..753.804 rows=356 loops=1)

2. 0.748 753.645 ↑ 47.0 356 1

Sort (cost=143,044.95..143,086.82 rows=16,747 width=2,035) (actual time=753.631..753.645 rows=356 loops=1)

  • Sort 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, ofmt.filter_name, ft.favourite_id, cb.cube_name, ((oum.is_editable)::character varying), (((max(obj_usergrp_map_1.is_editable) OVER (?)))::character varying), tm.tag_name, objcolmap.collection_id
  • Sort Method: quicksort Memory: 154kB
3. 31.861 752.897 ↑ 47.0 356 1

Merge Left Join (cost=134,181.40..141,870.01 rows=16,747 width=2,035) (actual time=552.591..752.897 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (objcolmap.obj_id)::text)
4. 0.142 629.165 ↑ 47.0 356 1

Merge Left Join (cost=134,180.98..135,345.57 rows=16,739 width=2,029) (actual time=547.985..629.165 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (obj_usergrp_map_1.object_id)::text)
5. 0.254 559.828 ↑ 1.9 356 1

Merge Left Join (cost=111,159.34..112,071.13 rows=687 width=1,997) (actual time=478.782..559.828 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (oum.object_id)::text)
6. 52.993 556.852 ↓ 12.3 356 1

Nested Loop (cost=105,894.71..106,775.86 rows=29 width=1,965) (actual time=476.111..556.852 rows=356 loops=1)

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 412248
7. 0.224 486.771 ↓ 71.2 356 1

Nested Loop Left Join (cost=105,894.71..105,956.96 rows=5 width=1,942) (actual time=475.060..486.771 rows=356 loops=1)

8. 0.279 483.343 ↓ 71.2 356 1

Nested Loop Left Join (cost=105,894.29..105,954.12 rows=5 width=1,935) (actual time=475.024..483.343 rows=356 loops=1)

9. 1.105 476.656 ↓ 71.2 356 1

Merge Join (cost=105,893.74..105,942.16 rows=5 width=1,892) (actual time=474.972..476.656 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (fo.object_id)::text)
10. 6.185 472.515 ↑ 7.2 970 1

Sort (cost=104,020.00..104,037.47 rows=6,988 width=1,892) (actual time=472.011..472.515 rows=970 loops=1)

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 284kB
11. 0.997 466.330 ↑ 7.2 971 1

Hash Left Join (cost=103,272.01..103,573.79 rows=6,988 width=1,892) (actual time=464.134..466.330 rows=971 loops=1)

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (tm.object_id)::text)
12. 6.895 455.438 ↑ 7.1 971 1

HashAggregate (cost=102,326.89..102,395.60 rows=6,871 width=2,352) (actual time=454.090..455.438 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
13. 0.077 448.543 ↑ 7.1 974 1

Append (cost=0.43..101,966.16 rows=6,871 width=2,352) (actual time=7.153..448.543 rows=974 loops=1)

14. 37.750 37.750 ↑ 11.1 9 1

Index Scan using named_selections_summ_tbl_ind on named_selections_summ_tbl (cost=0.43..17,740.18 rows=100 width=1,092) (actual time=7.151..37.750 rows=9 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (lower((org_name)::text) = 'gsk_ld'::text))
  • Filter: (lower((created_by)::text) = 'zicc232'::text)
  • Rows Removed by Filter: 20117
15. 1.298 1.298 ↑ 6.3 964 1

Index Scan using named_selections_summ_tbl_ind on named_selections_summ_tbl named_selections_summ_tbl_1 (cost=0.43..5,593.27 rows=6,112 width=1,092) (actual time=0.048..1.298 rows=964 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (lower((org_name)::text) = 'gsk_ld'::text) AND (scope = ANY ('{1,3,5}'::numeric[])))
16. 17.075 409.418 ↑ 659.0 1 1

Hash Join (cost=56,372.30..78,529.65 rows=659 width=1,092) (actual time=406.938..409.418 rows=1 loops=1)

  • Hash Cond: ((obj_user_map.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
17. 182.125 391.284 ↑ 6.0 164,435 1

HashAggregate (cost=55,491.25..65,284.55 rows=979,330 width=118) (actual time=343.908..391.284 rows=164,435 loops=1)

  • Group Key: obj_user_map.object_id
18. 15.762 209.159 ↑ 3.7 263,682 1

Append (cost=54.37..53,042.92 rows=979,330 width=118) (actual time=0.074..209.159 rows=263,682 loops=1)

19. 0.401 0.452 ↑ 16.1 300 1

Bitmap Heap Scan on obj_user_map (cost=54.37..4,452.07 rows=4,831 width=45) (actual time=0.072..0.452 rows=300 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=159
20. 0.051 0.051 ↑ 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.051..0.051 rows=306 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
21. 25.939 192.945 ↑ 3.7 263,382 1

Nested Loop (cost=540.94..33,900.91 rows=974,499 width=44) (actual time=0.105..192.945 rows=263,382 loops=1)

22. 0.088 0.142 ↑ 35.1 16 1

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

  • Group Key: adm_grp_usr_map.group_id
23. 0.029 0.054 ↑ 40.8 16 1

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

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
24. 0.025 0.025 ↑ 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.025..0.025 rows=16 loops=1)

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

Index Scan using idx705 on obj_usergrp_map (cost=0.43..51.21 rows=825 width=52) (actual time=0.012..10.429 rows=16,461 loops=16)

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
26. 0.355 1.059 ↑ 2.2 418 1

Hash (cost=869.34..869.34 rows=937 width=1,092) (actual time=1.059..1.059 rows=418 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 401kB
27. 0.704 0.704 ↑ 2.2 418 1

Index Scan using named_selections_summ_tbl_ind on named_selections_summ_tbl named_selections_summ_tbl_2 (cost=0.43..869.34 rows=937 width=1,092) (actual time=0.028..0.704 rows=418 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (lower((org_name)::text) = 'gsk_ld'::text) AND (scope = '2'::numeric))
28. 5.779 9.895 ↑ 1.0 28,761 1

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

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

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

30. 2.064 3.036 ↑ 7.5 360 1

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

  • Sort Key: fo.object_id
  • Sort Method: quicksort Memory: 53kB
31. 0.972 0.972 ↑ 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.084..0.972 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[]))
32. 6.408 6.408 ↑ 1.0 1 356

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

  • Index Cond: (object_id = (named_selections_summ_tbl.object_id)::text)
  • Heap Fetches: 356
33. 3.204 3.204 ↓ 0.0 0 356

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl ft (cost=0.41..0.56 rows=1 width=45) (actual time=0.009..0.009 rows=0 loops=356)

  • Index Cond: (((named_selections_summ_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
34. 15.666 17.088 ↑ 1.0 1,159 356

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

35. 1.422 1.422 ↑ 1.0 1,159 1

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

36. 0.745 2.722 ↑ 28.0 169 1

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

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 43kB
37. 0.015 1.977 ↑ 28.0 169 1

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

38. 0.211 1.962 ↑ 28.0 169 1

HashAggregate (cost=4,880.53..4,927.92 rows=4,739 width=85) (actual time=1.903..1.962 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
39. 0.163 1.751 ↑ 16.1 300 1

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

40. 1.126 1.588 ↑ 16.1 300 1

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

  • Sort Key: obj_user_map_1.user_id, obj_user_map_1.object_id
  • Sort Method: quicksort Memory: 67kB
41. 0.414 0.462 ↑ 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.067..0.462 rows=300 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=159
42. 0.048 0.048 ↑ 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.048..0.048 rows=306 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
43. 0.011 69.195 ↑ 1,624.3 3 1

Sort (cost=23,021.63..23,033.82 rows=4,873 width=76) (actual time=69.193..69.195 rows=3 loops=1)

  • Sort Key: obj_usergrp_map_1.object_id
  • Sort Method: quicksort Memory: 25kB
44. 0.118 69.184 ↑ 1,624.3 3 1

HashAggregate (cost=22,625.69..22,674.42 rows=4,873 width=76) (actual time=69.113..69.184 rows=3 loops=1)

  • Group Key: max(obj_usergrp_map_1.is_editable) OVER (?), obj_usergrp_map_1.object_id
45. 0.027 69.066 ↑ 286.6 17 1

WindowAgg (cost=22,516.05..22,601.32 rows=4,873 width=76) (actual time=69.054..69.066 rows=17 loops=1)

46. 0.000 69.039 ↑ 286.6 17 1

Sort (cost=22,516.05..22,528.23 rows=4,873 width=46) (actual time=69.038..69.039 rows=17 loops=1)

  • Sort Key: obj_usergrp_map_1.object_id
  • Sort Method: quicksort Memory: 26kB
47. 18.656 69.244 ↑ 286.6 17 1

Gather (cost=1,000.42..22,217.56 rows=4,873 width=46) (actual time=0.854..69.244 rows=17 loops=1)

  • Workers Planned: 9
  • Workers Launched: 9
48. 0.087 50.588 ↑ 270.5 2 10

Nested Loop Semi Join (cost=0.42..20,730.26 rows=541 width=46) (actual time=28.953..50.588 rows=2 loops=10)

49. 50.489 50.489 ↑ 89.2 8 10

Parallel Seq Scan on obj_usergrp_map obj_usergrp_map_1 (cost=0.00..20,256.89 rows=714 width=54) (actual time=26.006..50.489 rows=8 loops=10)

  • Filter: (lower((last_upd_by)::text) = 'zicc232'::text)
  • Rows Removed by Filter: 128489
50. 0.012 0.012 ↓ 0.0 0 80

Index Scan using grp_usr_map_usrid_grpid_i on adm_grp_usr_map adm_grp_usr_map_1 (cost=0.42..0.71 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=80)

  • Index Cond: ((lower((user_id)::text) = 'zicc232'::text) AND (group_id = obj_usergrp_map_1.group_id))
51. 91.871 91.871 ↑ 1.0 159,652 1

Index Only Scan using inx_objcollmap_objid_collid on object_collection_map objcolmap (cost=0.42..5,829.02 rows=160,947 width=48) (actual time=0.027..91.871 rows=159,652 loops=1)

  • Heap Fetches: 159652
Planning time : 7.200 ms
Execution time : 796.247 ms