explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dcH4

Settings
# exclusive inclusive rows x rows loops node
1. 1.483 708.419 ↑ 631.9 356 1

HashAggregate (cost=241,270.21..244,644.82 rows=224,974 width=2,131) (actual time=707.323..708.419 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, (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 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.          

CTE ns

3. 1.214 650.337 ↑ 6.7 971 1

HashAggregate (cost=145,930.68..145,995.76 rows=6,508 width=1,888) (actual time=650.004..650.337 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.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.069 649.123 ↑ 6.7 974 1

Append (cost=285.69..145,719.17 rows=6,508 width=1,888) (actual time=7.799..649.123 rows=974 loops=1)

5. 54.350 57.603 ↑ 10.4 9 1

Bitmap Heap Scan on named_selections_summ_tbl (cost=285.69..25,134.53 rows=94 width=151) (actual time=7.798..57.603 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=14328
6. 3.253 3.253 ↓ 1.0 24,570 1

Bitmap Index Scan on named_selections_summ_tbl_ind_4 (cost=0.00..285.67 rows=24,392 width=0) (actual time=3.252..3.253 rows=24,570 loops=1)

  • Index Cond: ((org_name)::text = 'GSK_LD'::text)
7. 48.000 50.924 ↑ 6.0 964 1

Bitmap Heap Scan on named_selections_summ_tbl named_selections_summ_tbl_1 (cost=287.11..25,105.45 rows=5,754 width=151) (actual time=4.895..50.924 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=14328
8. 2.924 2.924 ↓ 1.0 24,570 1

Bitmap Index Scan on named_selections_summ_tbl_ind_4 (cost=0.00..285.67 rows=24,392 width=0) (actual time=2.924..2.924 rows=24,570 loops=1)

  • Index Cond: ((org_name)::text = 'GSK_LD'::text)
9. 16.311 540.527 ↑ 660.0 1 1

Hash Join (cost=73,211.55..95,381.57 rows=660 width=151) (actual time=538.120..540.527 rows=1 loops=1)

  • Hash Cond: ((obj_user_map_1.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
10. 143.595 402.461 ↑ 6.0 164,436 1

HashAggregate (cost=55,553.15..65,352.05 rows=979,890 width=118) (actual time=366.011..402.461 rows=164,436 loops=1)

  • Group Key: obj_user_map_1.object_id
11. 15.735 258.866 ↑ 3.7 263,685 1

Append (cost=54.36..53,103.42 rows=979,890 width=118) (actual time=0.060..258.866 rows=263,685 loops=1)

12. 0.410 0.453 ↑ 16.1 300 1

Bitmap Heap Scan on obj_user_map obj_user_map_1 (cost=54.36..4,451.02 rows=4,830 width=45) (actual time=0.059..0.453 rows=300 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=160
13. 0.043 0.043 ↑ 15.9 303 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=303 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
14. 32.339 242.678 ↑ 3.7 263,385 1

Nested Loop (cost=542.02..33,954.05 rows=975,060 width=44) (actual time=0.088..242.678 rows=263,385 loops=1)

15. 0.028 0.083 ↑ 35.2 16 1

HashAggregate (cost=541.59..547.22 rows=563 width=8) (actual time=0.063..0.083 rows=16 loops=1)

  • Group Key: adm_grp_usr_map_1.group_id
16. 0.032 0.055 ↑ 40.9 16 1

Bitmap Heap Scan on adm_grp_usr_map adm_grp_usr_map_1 (cost=8.79..539.96 rows=654 width=8) (actual time=0.027..0.055 rows=16 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
17. 0.023 0.023 ↑ 40.9 16 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..8.62 rows=654 width=0) (actual time=0.023..0.023 rows=16 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
18. 210.256 210.256 ↓ 20.0 16,462 16

Index Scan using idx705 on obj_usergrp_map obj_usergrp_map_1 (cost=0.43..51.09 rows=825 width=52) (actual time=0.014..13.141 rows=16,462 loops=16)

  • Index Cond: (group_id = adm_grp_usr_map_1.group_id)
19. 0.160 121.755 ↑ 2.1 418 1

Hash (cost=17,647.37..17,647.37 rows=882 width=151) (actual time=121.754..121.755 rows=418 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 79kB
20. 1.523 121.595 ↑ 2.1 418 1

Bitmap Heap Scan on named_selections_summ_tbl named_selections_summ_tbl_2 (cost=16,670.02..17,647.37 rows=882 width=151) (actual time=120.237..121.595 rows=418 loops=1)

  • Recheck Cond: (((org_name)::text = 'GSK_LD'::text) AND (object_type = '7'::numeric) AND (scope = '2'::numeric))
  • Heap Blocks: exact=387
21. 0.823 120.072 ↓ 0.0 0 1

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

22. 3.288 3.288 ↓ 1.0 24,570 1

Bitmap Index Scan on named_selections_summ_tbl_ind_4 (cost=0.00..285.67 rows=24,392 width=0) (actual time=3.288..3.288 rows=24,570 loops=1)

  • Index Cond: ((org_name)::text = 'GSK_LD'::text)
23. 115.961 115.961 ↑ 1.3 35,551 1

Bitmap Index Scan on named_selections_summ_tbl_ind (cost=0.00..16,383.66 rows=47,360 width=0) (actual time=115.961..115.961 rows=35,551 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (scope = '2'::numeric))
24. 0.096 706.936 ↑ 631.9 356 1

Merge Right Join (cost=77,825.15..82,338.44 rows=224,974 width=2,131) (actual time=706.828..706.936 rows=356 loops=1)

  • Merge Cond: ((oum.object_id)::text = (ns.object_id)::text)
25. 0.007 665.770 ↓ 0.0 0 1

Sort (cost=1,087.24..1,093.27 rows=2,415 width=77) (actual time=665.770..665.770 rows=0 loops=1)

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 25kB
26. 0.001 665.763 ↓ 0.0 0 1

Subquery Scan on oum (cost=903.24..951.54 rows=2,415 width=77) (actual time=665.763..665.763 rows=0 loops=1)

27. 0.019 665.762 ↓ 0.0 0 1

HashAggregate (cost=903.24..927.39 rows=2,415 width=85) (actual time=665.762..665.762 rows=0 loops=1)

  • Group Key: max(obj_user_map.is_editable) OVER (?), obj_user_map.object_id, obj_user_map.user_id
28. 0.003 665.743 ↓ 0.0 0 1

WindowAgg (cost=836.83..885.13 rows=2,415 width=85) (actual time=665.743..665.743 rows=0 loops=1)

29. 0.004 665.740 ↓ 0.0 0 1

Sort (cost=836.83..842.86 rows=2,415 width=55) (actual time=665.740..665.740 rows=0 loops=1)

  • Sort Key: obj_user_map.user_id, obj_user_map.object_id
  • Sort Method: quicksort Memory: 25kB
30. 0.000 665.736 ↓ 0.0 0 1

Nested Loop (cost=146.98..701.13 rows=2,415 width=55) (actual time=665.736..665.736 rows=0 loops=1)

31. 0.423 651.248 ↓ 4.9 971 1

HashAggregate (cost=146.43..148.43 rows=200 width=118) (actual time=651.070..651.248 rows=971 loops=1)

  • Group Key: (ns_1.object_id)::text
32. 650.825 650.825 ↑ 6.7 971 1

CTE Scan on ns ns_1 (cost=0.00..130.16 rows=6,508 width=118) (actual time=650.009..650.825 rows=971 loops=1)

33. 14.565 14.565 ↓ 0.0 0 971

Index Scan using obj_user_map_objid_usrid_i on obj_user_map (cost=0.55..2.75 rows=1 width=55) (actual time=0.015..0.015 rows=0 loops=971)

  • Index Cond: (((object_id)::text = (ns_1.object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
34. 1.762 41.070 ↑ 52.4 356 1

Sort (cost=76,737.91..76,784.51 rows=18,641 width=2,099) (actual time=41.052..41.070 rows=356 loops=1)

  • Sort Key: ns.object_id
  • Sort Method: quicksort Memory: 154kB
35. 0.109 39.308 ↑ 52.4 356 1

Hash Right Join (cost=71,856.32..75,415.69 rows=18,641 width=2,099) (actual time=39.005..39.308 rows=356 loops=1)

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (ns.object_id)::text)
36. 1.052 16.117 ↑ 64,248.5 2 1

HashAggregate (cost=68,481.05..69,766.02 rows=128,497 width=76) (actual time=15.912..16.117 rows=2 loops=1)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
37. 0.019 15.065 ↑ 28,678.2 17 1

WindowAgg (cost=57,511.63..66,043.40 rows=487,530 width=76) (actual time=15.058..15.065 rows=17 loops=1)

38. 0.014 15.046 ↑ 28,678.2 17 1

Sort (cost=57,511.63..58,730.45 rows=487,530 width=46) (actual time=15.045..15.046 rows=17 loops=1)

  • Sort Key: obj_usergrp_map.object_id
  • Sort Method: quicksort Memory: 26kB
39. 0.029 15.032 ↑ 28,678.2 17 1

Hash Join (cost=701.24..11,451.91 rows=487,530 width=46) (actual time=10.843..15.032 rows=17 loops=1)

  • Hash Cond: (obj_usergrp_map.group_id = adm_grp_usr_map.group_id)
40. 0.000 14.928 ↑ 4,199.2 153 1

Nested Loop (cost=146.98..3,787.36 rows=642,484 width=54) (actual time=9.406..14.928 rows=153 loops=1)

41. 0.464 0.591 ↓ 4.9 971 1

HashAggregate (cost=146.43..148.43 rows=200 width=118) (actual time=0.442..0.591 rows=971 loops=1)

  • Group Key: (ns_2.object_id)::text
42. 0.127 0.127 ↑ 6.7 971 1

CTE Scan on ns ns_2 (cost=0.00..130.16 rows=6,508 width=118) (actual time=0.002..0.127 rows=971 loops=1)

43. 14.565 14.565 ↓ 0.0 0 971

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

  • Index Cond: ((object_id)::text = (ns_2.object_id)::text)
44. 0.005 0.075 ↑ 35.2 16 1

Hash (cost=547.22..547.22 rows=563 width=8) (actual time=0.075..0.075 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.014 0.070 ↑ 35.2 16 1

HashAggregate (cost=541.59..547.22 rows=563 width=8) (actual time=0.064..0.070 rows=16 loops=1)

  • Group Key: adm_grp_usr_map.group_id
46. 0.031 0.056 ↑ 40.9 16 1

Bitmap Heap Scan on adm_grp_usr_map (cost=8.79..539.96 rows=654 width=8) (actual time=0.031..0.056 rows=16 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
47. 0.025 0.025 ↑ 40.9 16 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..8.62 rows=654 width=0) (actual time=0.025..0.025 rows=16 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
48. 0.166 23.082 ↓ 12.3 356 1

Hash (cost=3,374.90..3,374.90 rows=29 width=2,067) (actual time=23.082..23.082 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 97kB
49. 0.000 22.916 ↓ 12.3 356 1

Nested Loop Left Join (cost=2,634.46..3,374.90 rows=29 width=2,067) (actual time=18.333..22.916 rows=356 loops=1)

50. 0.577 19.389 ↓ 12.3 356 1

Hash Join (cost=2,634.04..3,354.72 rows=29 width=2,061) (actual time=18.307..19.389 rows=356 loops=1)

  • Hash Cond: ((cb.cube_id)::numeric = ns.cube_id)
51. 1.324 1.324 ↑ 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.324 rows=1,159 loops=1)

52. 0.132 17.488 ↓ 71.2 356 1

Hash (cost=2,633.98..2,633.98 rows=5 width=2,038) (actual time=17.488..17.488 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 92kB
53. 0.274 17.356 ↓ 71.2 356 1

Nested Loop Left Join (cost=2,596.47..2,633.98 rows=5 width=2,038) (actual time=8.301..17.356 rows=356 loops=1)

54. 0.136 14.234 ↓ 71.2 356 1

Nested Loop Left Join (cost=2,596.05..2,631.10 rows=5 width=2,031) (actual time=8.280..14.234 rows=356 loops=1)

55. 0.050 8.402 ↓ 71.2 356 1

Merge Left Join (cost=2,595.50..2,623.72 rows=5 width=1,988) (actual time=8.246..8.402 rows=356 loops=1)

  • Merge Cond: ((ns.object_id)::text = (vis_translation_custom_object.object_id)::text)
56. 0.086 8.350 ↓ 71.2 356 1

Merge Left Join (cost=2,595.36..2,619.88 rows=5 width=1,924) (actual time=8.241..8.350 rows=356 loops=1)

  • Merge Cond: ((ns.object_id)::text = (st1.object_id)::text)
57. 1.567 8.258 ↓ 71.2 356 1

Sort (cost=2,595.21..2,595.23 rows=5 width=1,892) (actual time=8.231..8.258 rows=356 loops=1)

  • Sort Key: ns.object_id
  • Sort Method: quicksort Memory: 119kB
58. 3.718 6.691 ↓ 71.2 356 1

Hash Right Join (cost=1,901.64..2,595.15 rows=5 width=1,892) (actual time=6.620..6.691 rows=356 loops=1)

  • Hash Cond: ((tm.object_id)::text = (ns.object_id)::text)
59. 1.938 1.938 ↑ 1.0 28,761 1

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

60. 0.102 1.035 ↓ 71.2 356 1

Hash (cost=1,901.58..1,901.58 rows=5 width=1,888) (actual time=1.035..1.035 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
61. 0.185 0.933 ↓ 71.2 356 1

Hash Join (cost=1,754.33..1,901.58 rows=5 width=1,888) (actual time=0.669..0.933 rows=356 loops=1)

  • Hash Cond: ((ns.object_id)::text = (fo.object_id)::text)
62. 0.091 0.091 ↑ 6.7 971 1

CTE Scan on ns (cost=0.00..130.16 rows=6,508 width=1,888) (actual time=0.001..0.091 rows=971 loops=1)

63. 0.099 0.657 ↑ 7.5 360 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 60kB
64. 0.558 0.558 ↑ 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.558 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[]))
65. 0.000 0.006 ↓ 0.0 0 1

GroupAggregate (cost=0.14..22.22 rows=190 width=150) (actual time=0.006..0.006 rows=0 loops=1)

  • Group Key: st1.object_id
66. 0.001 0.006 ↓ 0.0 0 1

Unique (cost=0.14..16.05 rows=190 width=120) (actual time=0.005..0.006 rows=0 loops=1)

67. 0.005 0.005 ↓ 0.0 0 1

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

  • Heap Fetches: 0
68. 0.001 0.002 ↓ 0.0 0 1

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

  • Group Key: vis_translation_custom_object.object_id
69. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

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

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

  • Index Cond: (object_id = (ns.object_id)::text)
  • Heap Fetches: 0
71. 2.848 2.848 ↓ 0.0 0 356

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

  • Index Cond: (((ns.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
72. 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.69 rows=1 width=48) (actual time=0.010..0.010 rows=0 loops=356)

  • Index Cond: (obj_id = (ns.object_id)::text)
  • Heap Fetches: 0
Planning time : 8.648 ms
Execution time : 739.911 ms