explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yBLn

Settings
# exclusive inclusive rows x rows loops node
1. 7.358 6,909.491 ↑ 1,625.0 356 1

HashAggregate (cost=489,549.15..498,226.68 rows=578,502 width=2,131) (actual time=6,902.659..6,909.491 rows=356 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, 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, (string_agg(((st.locale_id)::character varying)::text, ','::text ORDER BY st.locale_id)), (min(CASE WHEN ((vis_translation_custom_object.field)::text = 'title'::text) THEN vis_translation_custom_object.translation ELSE NULL::text END)), (min(CASE WHEN ((vis_translation_custom_object.field)::text = 'description'::text) THEN vis_translation_custom_object.translation ELSE NULL::text END))
2. 0.198 6,902.133 ↑ 1,625.0 356 1

Merge Right Join (cost=444,689.66..456,285.28 rows=578,502 width=2,131) (actual time=6,901.919..6,902.133 rows=356 loops=1)

  • Merge Cond: ((oum.object_id)::text = (named_selections_summ_tbl.object_id)::text)
3. 0.695 2.639 ↑ 28.0 169 1

Sort (cost=5,263.41..5,275.25 rows=4,738 width=77) (actual time=2.621..2.639 rows=169 loops=1)

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 43kB
4. 0.014 1.944 ↑ 28.0 169 1

Subquery Scan on oum (cost=4,879.39..4,974.15 rows=4,738 width=77) (actual time=1.884..1.944 rows=169 loops=1)

5. 0.140 1.930 ↑ 28.0 169 1

HashAggregate (cost=4,879.39..4,926.77 rows=4,738 width=85) (actual time=1.884..1.930 rows=169 loops=1)

  • Group Key: max(obj_user_map.is_editable) OVER (?), obj_user_map.object_id, obj_user_map.user_id
6. 0.156 1.790 ↑ 16.1 300 1

WindowAgg (cost=4,746.57..4,843.17 rows=4,830 width=85) (actual time=1.630..1.790 rows=300 loops=1)

7. 1.109 1.634 ↑ 16.1 300 1

Sort (cost=4,746.57..4,758.64 rows=4,830 width=55) (actual time=1.622..1.634 rows=300 loops=1)

  • Sort Key: obj_user_map.user_id, obj_user_map.object_id
  • Sort Method: quicksort Memory: 67kB
8. 0.482 0.525 ↑ 16.1 300 1

Bitmap Heap Scan on obj_user_map (cost=54.36..4,451.02 rows=4,830 width=55) (actual time=0.062..0.525 rows=300 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=159
9. 0.043 0.043 ↑ 16.0 301 1

Bitmap Index Scan on obj_user_map_user_id_lower (cost=0.00..53.15 rows=4,830 width=0) (actual time=0.043..0.043 rows=301 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
10. 0.519 6,899.296 ↑ 68.6 356 1

Sort (cost=439,426.25..439,487.32 rows=24,425 width=2,099) (actual time=6,899.281..6,899.296 rows=356 loops=1)

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 154kB
11. 0.485 6,898.777 ↑ 68.6 356 1

Hash Left Join (cost=399,872.51..437,646.15 rows=24,425 width=2,099) (actual time=5,485.482..6,898.777 rows=356 loops=1)

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (obj_usergrp_map_1.object_id)::text)
12. 0.161 3,989.594 ↓ 9.4 356 1

Nested Loop Left Join (cost=242,950.48..279,869.52 rows=38 width=2,067) (actual time=2,576.523..3,989.594 rows=356 loops=1)

13. 51.994 3,985.873 ↓ 9.4 356 1

Nested Loop (cost=242,950.06..279,842.02 rows=38 width=2,061) (actual time=2,576.489..3,985.873 rows=356 loops=1)

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 412248
14. 0.095 3,917.147 ↓ 71.2 356 1

Nested Loop Left Join (cost=242,950.06..278,992.52 rows=5 width=2,038) (actual time=2,575.624..3,917.147 rows=356 loops=1)

15. 0.077 3,913.848 ↓ 71.2 356 1

Nested Loop Left Join (cost=242,949.65..278,989.57 rows=5 width=2,031) (actual time=2,575.597..3,913.848 rows=356 loops=1)

16. 0.084 3,907.719 ↓ 71.2 356 1

Merge Left Join (cost=242,949.10..278,982.10 rows=5 width=1,988) (actual time=2,575.549..3,907.719 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (vis_translation_custom_object.object_id)::text)
17. 342.690 3,907.616 ↓ 71.2 356 1

Merge Left Join (cost=242,948.95..278,978.26 rows=5 width=1,924) (actual time=2,575.522..3,907.616 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (nst.object_id)::text)
18. 1.582 593.742 ↓ 71.2 356 1

Sort (cost=74,113.03..74,113.04 rows=5 width=1,892) (actual time=593.657..593.742 rows=356 loops=1)

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 119kB
19. 0.206 592.160 ↓ 71.2 356 1

Hash Join (cost=73,840.28..74,112.97 rows=5 width=1,892) (actual time=591.049..592.160 rows=356 loops=1)

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (fo.object_id)::text)
20. 0.244 591.285 ↑ 6.1 971 1

Hash Left Join (cost=72,085.94..72,343.01 rows=5,953 width=1,892) (actual time=590.363..591.285 rows=971 loops=1)

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (tm.object_id)::text)
21. 4.367 583.002 ↑ 6.0 971 1

HashAggregate (cost=71,140.82..71,199.35 rows=5,853 width=2,352) (actual time=582.289..583.002 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
22. 0.067 578.635 ↑ 6.0 974 1

Append (cost=284.59..70,833.54 rows=5,853 width=2,352) (actual time=7.380..578.635 rows=974 loops=1)

23. 52.442 55.637 ↑ 10.4 9 1

Bitmap Heap Scan on named_selections_summ_tbl (cost=284.59..25,133.43 rows=94 width=1,092) (actual time=7.379..55.637 rows=9 loops=1)

  • Recheck Cond: ((org_name)::text = 'GSK_LD'::text)
  • Filter: ((object_type = '7'::numeric) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 24245
  • Heap Blocks: exact=14090
24. 3.195 3.195 ↑ 1.0 24,254 1

Bitmap Index Scan on named_selections_summ_tbl_ind_4 (cost=0.00..284.57 rows=24,392 width=0) (actual time=3.195..3.195 rows=24,254 loops=1)

  • Index Cond: ((org_name)::text = 'GSK_LD'::text)
25. 33.900 36.680 ↑ 6.0 964 1

Bitmap Heap Scan on named_selections_summ_tbl named_selections_summ_tbl_1 (cost=286.01..25,104.35 rows=5,754 width=1,092) (actual time=4.458..36.680 rows=964 loops=1)

  • Recheck Cond: ((org_name)::text = 'GSK_LD'::text)
  • Filter: ((object_type = '7'::numeric) AND (scope = ANY ('{1,3,5}'::numeric[])))
  • Rows Removed by Filter: 23290
  • Heap Blocks: exact=14090
26. 2.780 2.780 ↑ 1.0 24,254 1

Bitmap Index Scan on named_selections_summ_tbl_ind_4 (cost=0.00..284.57 rows=24,392 width=0) (actual time=2.780..2.780 rows=24,254 loops=1)

  • Index Cond: ((org_name)::text = 'GSK_LD'::text)
27. 0.230 486.251 ↑ 5.0 1 1

Hash Join (cost=19,528.29..20,507.96 rows=5 width=1,092) (actual time=486.065..486.251 rows=1 loops=1)

  • Hash Cond: ((named_selections_summ_tbl_2.object_id)::text = (obj_user_map_1.object_id)::text)
28. 0.950 99.479 ↑ 2.1 418 1

Bitmap Heap Scan on named_selections_summ_tbl named_selections_summ_tbl_2 (cost=16,643.62..17,620.97 rows=882 width=1,092) (actual time=98.684..99.479 rows=418 loops=1)

  • Recheck Cond: (((org_name)::text = 'GSK_LD'::text) AND (object_type = '7'::numeric) AND (scope = '2'::numeric))
  • Heap Blocks: exact=338
29. 0.686 98.529 ↓ 0.0 0 1

BitmapAnd (cost=16,643.62..16,643.62 rows=882 width=0) (actual time=98.529..98.529 rows=0 loops=1)

30. 3.559 3.559 ↑ 1.0 24,254 1

Bitmap Index Scan on named_selections_summ_tbl_ind_4 (cost=0.00..284.57 rows=24,392 width=0) (actual time=3.559..3.559 rows=24,254 loops=1)

  • Index Cond: ((org_name)::text = 'GSK_LD'::text)
31. 94.284 94.284 ↑ 1.4 33,964 1

Bitmap Index Scan on named_selections_summ_tbl_ind (cost=0.00..16,358.36 rows=47,360 width=0) (actual time=94.284..94.284 rows=33,964 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (scope = '2'::numeric))
32. 30.451 386.542 ↓ 21.7 164,435 1

Hash (cost=2,789.77..2,789.77 rows=7,592 width=118) (actual time=386.541..386.542 rows=164,435 loops=1)

  • Buckets: 262144 (originally 8192) Batches: 1 (originally 1) Memory Usage: 14633kB
33. 119.462 356.091 ↓ 21.7 164,435 1

HashAggregate (cost=2,637.93..2,713.85 rows=7,592 width=118) (actual time=325.245..356.091 rows=164,435 loops=1)

  • Group Key: obj_user_map_1.object_id
34. 14.311 236.629 ↓ 34.7 263,682 1

Append (cost=0.42..2,618.95 rows=7,592 width=118) (actual time=0.029..236.629 rows=263,682 loops=1)

35. 0.390 0.390 ↓ 5.3 300 1

Index Scan using obj_user_map_user_id_1 on obj_user_map obj_user_map_1 (cost=0.42..65.22 rows=57 width=45) (actual time=0.028..0.390 rows=300 loops=1)

  • Index Cond: ((user_id)::text = 'zicc232'::text)
36. 31.688 221.928 ↓ 35.0 263,382 1

Nested Loop (cost=0.84..2,439.85 rows=7,535 width=44) (actual time=0.040..221.928 rows=263,382 loops=1)

37. 0.032 0.032 ↓ 5.3 16 1

Index Only Scan using uk_usrgrp on adm_grp_usr_map (cost=0.42..1.57 rows=3 width=8) (actual time=0.018..0.032 rows=16 loops=1)

  • Index Cond: (user_id = 'zicc232'::text)
  • Heap Fetches: 0
38. 190.208 190.208 ↓ 20.0 16,461 16

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

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
39. 4.066 8.039 ↑ 1.0 28,761 1

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

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

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

41. 0.094 0.669 ↑ 7.5 360 1

Hash (cost=1,720.76..1,720.76 rows=2,686 width=44) (actual time=0.669..0.669 rows=360 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 60kB
42. 0.575 0.575 ↑ 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.024..0.575 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[]))
43. 769.053 2,971.184 ↓ 6,509.7 1,301,949 1

GroupAggregate (cost=168,835.92..204,862.65 rows=200 width=76) (actual time=1,949.811..2,971.184 rows=1,301,949 loops=1)

  • Group Key: nst.object_id
44. 196.673 2,202.131 ↑ 1.0 1,301,950 1

Unique (cost=168,835.92..178,660.71 rows=1,309,972 width=46) (actual time=1,949.771..2,202.131 rows=1,301,950 loops=1)

45. 1,667.179 2,005.458 ↑ 1.0 1,301,950 1

Sort (cost=168,835.92..172,110.85 rows=1,309,972 width=46) (actual time=1,949.768..2,005.458 rows=1,301,950 loops=1)

  • Sort Key: nst.object_id, st.locale_id
  • Sort Method: quicksort Memory: 151631kB
46. 103.205 338.279 ↑ 1.0 1,309,972 1

Merge Left Join (cost=0.70..35,735.53 rows=1,309,972 width=46) (actual time=0.034..338.279 rows=1,309,972 loops=1)

  • Merge Cond: ((nst.object_id)::text = (st.object_id)::text)
47. 235.070 235.070 ↑ 1.0 1,309,972 1

Index Only Scan using named_selections_summ_tbl_pkey on named_selections_summ_tbl nst (cost=0.55..32,443.13 rows=1,309,972 width=44) (actual time=0.028..235.070 rows=1,309,972 loops=1)

  • Heap Fetches: 31
48. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using pk_vis_trans_cus_obj on vis_translation_custom_object st (cost=0.14..15.10 rows=190 width=120) (actual time=0.004..0.004 rows=0 loops=1)

  • Heap Fetches: 0
49. 0.003 0.019 ↓ 0.0 0 1

GroupAggregate (cost=0.14..3.80 rows=1 width=182) (actual time=0.019..0.019 rows=0 loops=1)

  • Group Key: vis_translation_custom_object.object_id
50. 0.016 0.016 ↓ 0.0 0 1

Index Scan using pk_vis_trans_cus_obj on vis_translation_custom_object (cost=0.14..3.78 rows=1 width=268) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: (locale_id = '2'::smallint)
51. 6.052 6.052 ↑ 1.0 1 356

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

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

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl ft (cost=0.41..0.58 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))
53. 15.522 16.732 ↑ 1.3 1,159 356

Materialize (cost=0.00..724.98 rows=1,465 width=31) (actual time=0.000..0.047 rows=1,159 loops=356)

54. 1.210 1.210 ↑ 1.3 1,159 1

Seq Scan on cube_tbl cb (cost=0.00..717.65 rows=1,465 width=31) (actual time=0.009..1.210 rows=1,159 loops=1)

55. 3.560 3.560 ↓ 0.0 0 356

Index Only Scan using inx_objcollmap_objid_collid on object_collection_map objcolmap (cost=0.42..0.71 rows=1 width=48) (actual time=0.010..0.010 rows=0 loops=356)

  • Index Cond: (obj_id = (named_selections_summ_tbl.object_id)::text)
  • Heap Fetches: 0
56. 36.172 2,908.698 ↓ 1.3 164,302 1

Hash (cost=155,315.81..155,315.81 rows=128,497 width=76) (actual time=2,908.698..2,908.698 rows=164,302 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 14944kB
57. 103.928 2,872.526 ↓ 1.3 164,302 1

HashAggregate (cost=152,745.87..154,030.84 rows=128,497 width=76) (actual time=2,839.576..2,872.526 rows=164,302 loops=1)

  • Group Key: max(obj_usergrp_map_1.is_editable) OVER (?), obj_usergrp_map_1.object_id
58. 135.933 2,768.598 ↑ 3.7 263,382 1

WindowAgg (cost=130,822.46..147,874.00 rows=974,374 width=76) (actual time=2,599.473..2,768.598 rows=263,382 loops=1)

59. 2,380.444 2,632.665 ↑ 3.7 263,382 1

Sort (cost=130,822.46..133,258.39 rows=974,374 width=46) (actual time=2,599.456..2,632.665 rows=263,382 loops=1)

  • Sort Key: obj_usergrp_map_1.object_id
  • Sort Method: quicksort Memory: 39431kB
60. 68.473 252.221 ↑ 3.7 263,382 1

Nested Loop (cost=540.94..33,900.91 rows=974,374 width=46) (actual time=0.097..252.221 rows=263,382 loops=1)

61. 0.031 0.084 ↑ 35.1 16 1

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

  • Group Key: adm_grp_usr_map_1.group_id
62. 0.029 0.053 ↑ 40.8 16 1

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

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

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

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

  • Index Cond: (group_id = adm_grp_usr_map_1.group_id)
Planning time : 6.890 ms
Execution time : 6,934.155 ms