explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VFTW

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 125.304 ↓ 0.0 0 1

Unique (cost=20,322.09..20,344.14 rows=420 width=2,020) (actual time=125.304..125.304 rows=0 loops=1)

2. 0.013 125.303 ↓ 0.0 0 1

Sort (cost=20,322.09..20,323.14 rows=420 width=2,020) (actual time=125.303..125.303 rows=0 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.is_editable) OVER (?)))::character varying), tm.tag_name, objcolmap.collection_id
  • Sort Method: quicksort Memory: 25kB
3. 0.000 125.290 ↓ 0.0 0 1

Hash Left Join (cost=20,092.50..20,303.79 rows=420 width=2,020) (actual time=125.290..125.290 rows=0 loops=1)

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (objcolmap.obj_id)::text)
4. 0.017 125.290 ↓ 0.0 0 1

Hash Right Join (cost=19,864.97..20,067.86 rows=418 width=2,015) (actual time=125.290..125.290 rows=0 loops=1)

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (named_selections_summ_tbl.object_id)::text)
5. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=944.20..1,027.87 rows=8,367 width=77) (never executed)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
6. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=755.94..902.36 rows=8,367 width=77) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Sort (cost=755.94..776.86 rows=8,367 width=47) (never executed)

  • Sort Key: obj_usergrp_map.object_id
8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=31.96..210.81 rows=8,367 width=47) (never executed)

9. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=31.67..31.96 rows=29 width=8) (never executed)

  • Group Key: adm_grp_usr_map.group_id
10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on adm_grp_usr_map (cost=1.62..31.60 rows=30 width=8) (never executed)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..1.61 rows=30 width=0) (never executed)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx705 on obj_usergrp_map (cost=0.29..5.85 rows=32 width=55) (never executed)

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
13. 0.000 125.273 ↓ 0.0 0 1

Hash (cost=18,920.64..18,920.64 rows=10 width=1,983) (actual time=125.273..125.273 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 0.001 125.273 ↓ 0.0 0 1

Nested Loop (cost=18,604.60..18,920.64 rows=10 width=1,983) (actual time=125.273..125.273 rows=0 loops=1)

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
15. 0.000 125.272 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,604.60..18,626.74 rows=1 width=1,966) (actual time=125.272..125.272 rows=0 loops=1)

  • Join Filter: ((named_selections_summ_tbl.object_id)::text = (tm.object_id)::text)
16. 0.001 125.272 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,604.60..18,608.52 rows=1 width=1,960) (actual time=125.272..125.272 rows=0 loops=1)

17. 0.000 125.271 ↓ 0.0 0 1

Nested Loop Left Join (cost=18,604.32..18,606.33 rows=1 width=1,955) (actual time=125.271..125.271 rows=0 loops=1)

18. 0.002 125.271 ↓ 0.0 0 1

Merge Left Join (cost=18,603.91..18,604.17 rows=1 width=1,920) (actual time=125.271..125.271 rows=0 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (oum.object_id)::text)
19. 0.004 125.269 ↓ 0.0 0 1

Sort (cost=18,553.67..18,553.68 rows=1 width=1,888) (actual time=125.269..125.269 rows=0 loops=1)

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 25kB
20. 0.001 125.265 ↓ 0.0 0 1

Nested Loop (cost=18,535.02..18,553.66 rows=1 width=1,888) (actual time=125.264..125.265 rows=0 loops=1)

21. 0.001 125.264 ↓ 0.0 0 1

HashAggregate (cost=18,534.60..18,534.67 rows=7 width=2,226) (actual time=125.264..125.264 rows=0 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.last_accessed
22. 0.003 125.263 ↓ 0.0 0 1

Append (cost=0.00..18,534.27 rows=7 width=2,226) (actual time=125.262..125.263 rows=0 loops=1)

23. 49.630 49.630 ↓ 0.0 0 1

Seq Scan on named_selections_summ_tbl (cost=0.00..6,023.94 rows=1 width=960) (actual time=49.630..49.630 rows=0 loops=1)

  • Filter: ((object_type = '7'::numeric) AND (lower((org_name)::text) = 'gsk_ld'::text) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 35331
24. 38.059 38.059 ↓ 0.0 0 1

Seq Scan on named_selections_summ_tbl named_selections_summ_tbl_1 (cost=0.00..5,980.11 rows=5 width=960) (actual time=38.059..38.059 rows=0 loops=1)

  • Filter: ((object_type = '7'::numeric) AND (scope = ANY ('{1,3,5}'::numeric[])) AND (lower((org_name)::text) = 'gsk_ld'::text))
  • Rows Removed by Filter: 35331
25. 0.020 37.571 ↓ 0.0 0 1

Hash Join (cost=6,339.74..6,530.11 rows=1 width=960) (actual time=37.570..37.571 rows=0 loops=1)

  • Hash Cond: ((obj_user_map.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
26. 0.277 0.843 ↑ 8,414.0 1 1

HashAggregate (cost=403.45..487.59 rows=8,414 width=118) (actual time=0.843..0.843 rows=1 loops=1)

  • Group Key: obj_user_map.object_id
27. 0.044 0.566 ↑ 11.6 727 1

Append (cost=1.75..382.42 rows=8,414 width=118) (actual time=0.059..0.566 rows=727 loops=1)

28. 0.216 0.265 ↓ 11.2 528 1

Bitmap Heap Scan on obj_user_map (cost=1.75..45.40 rows=47 width=43) (actual time=0.059..0.265 rows=528 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=55
29. 0.049 0.049 ↓ 11.3 532 1

Bitmap Index Scan on obj_user_map_user_id_lower (cost=0.00..1.74 rows=47 width=0) (actual time=0.049..0.049 rows=532 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
30. 0.016 0.257 ↑ 42.0 199 1

Nested Loop (cost=31.96..210.81 rows=8,367 width=45) (actual time=0.073..0.257 rows=199 loops=1)

31. 0.014 0.055 ↓ 1.1 31 1

HashAggregate (cost=31.67..31.96 rows=29 width=8) (actual time=0.052..0.055 rows=31 loops=1)

  • Group Key: adm_grp_usr_map_1.group_id
32. 0.022 0.041 ↓ 1.0 31 1

Bitmap Heap Scan on adm_grp_usr_map adm_grp_usr_map_1 (cost=1.62..31.60 rows=30 width=8) (actual time=0.023..0.041 rows=31 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=9
33. 0.019 0.019 ↓ 1.0 31 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..1.61 rows=30 width=0) (actual time=0.019..0.019 rows=31 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
34. 0.186 0.186 ↑ 5.3 6 31

Index Scan using idx705 on obj_usergrp_map obj_usergrp_map_1 (cost=0.29..5.85 rows=32 width=53) (actual time=0.003..0.006 rows=6 loops=31)

  • Index Cond: (group_id = adm_grp_usr_map_1.group_id)
35. 0.002 36.708 ↓ 0.0 0 1

Hash (cost=5,936.28..5,936.28 rows=1 width=960) (actual time=36.707..36.708 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
36. 36.706 36.706 ↓ 0.0 0 1

Seq Scan on named_selections_summ_tbl named_selections_summ_tbl_2 (cost=0.00..5,936.28 rows=1 width=960) (actual time=36.706..36.706 rows=0 loops=1)

  • Filter: ((object_type = '7'::numeric) AND (scope = '2'::numeric) AND (lower((org_name)::text) = 'gsk_ld'::text))
  • Rows Removed by Filter: 35331
37. 0.000 0.000 ↓ 0.0 0

Index Scan using folder_obj_map_pkey on folder_obj_map fo (cost=0.42..2.70 rows=1 width=43) (never executed)

  • Index Cond: ((object_id)::text = (named_selections_summ_tbl.object_id)::text)
  • Filter: (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[]))
38. 0.000 0.000 ↓ 0.0 0

Sort (cost=50.24..50.36 rows=47 width=75) (never executed)

  • Sort Key: oum.object_id
39. 0.000 0.000 ↓ 0.0 0

Subquery Scan on oum (cost=47.99..48.93 rows=47 width=75) (never executed)

40. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=47.99..48.46 rows=47 width=293) (never executed)

  • Group Key: max(obj_user_map_1.is_editable) OVER (?), obj_user_map_1.object_id, obj_user_map_1.user_id
41. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=46.70..47.64 rows=47 width=293) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Sort (cost=46.70..46.82 rows=47 width=263) (never executed)

  • Sort Key: obj_user_map_1.user_id, obj_user_map_1.object_id
43. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on obj_user_map obj_user_map_1 (cost=1.75..45.40 rows=47 width=263) (never executed)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
44. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on obj_user_map_user_id_lower (cost=0.00..1.74 rows=47 width=0) (never executed)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using inx_objftrmaptbl_objidftrname on obj_filter_map_tbl ofmt (cost=0.41..2.15 rows=1 width=79) (never executed)

  • Index Cond: (object_id = (named_selections_summ_tbl.object_id)::text)
  • Heap Fetches: 0
46. 0.000 0.000 ↓ 0.0 0

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl ft (cost=0.28..2.18 rows=1 width=43) (never executed)

  • Index Cond: (((named_selections_summ_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
47. 0.000 0.000 ↓ 0.0 0

Seq Scan on tag_master tm (cost=0.00..11.43 rows=543 width=50) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on cube_tbl cb (cost=0.00..267.56 rows=1,756 width=25) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Hash (cost=143.35..143.35 rows=6,735 width=44) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Seq Scan on object_collection_map objcolmap (cost=0.00..143.35 rows=6,735 width=44) (never executed)

Planning time : 6.458 ms
Execution time : 125.717 ms