explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qLE0

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 287.817 ↑ 7.1 59 1

Unique (cost=20,322.09..20,344.14 rows=420 width=2,020) (actual time=287.789..287.817 rows=59 loops=1)

2. 0.158 287.791 ↑ 7.1 59 1

Sort (cost=20,322.09..20,323.14 rows=420 width=2,020) (actual time=287.788..287.791 rows=59 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: 40kB
3. 0.075 287.633 ↑ 7.1 59 1

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

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (objcolmap.obj_id)::text)
4. 0.046 285.050 ↑ 7.1 59 1

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

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (named_selections_summ_tbl.object_id)::text)
5. 0.149 0.925 ↑ 51.3 163 1

HashAggregate (cost=944.20..1,027.87 rows=8,367 width=77) (actual time=0.849..0.925 rows=163 loops=1)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
6. 0.116 0.776 ↑ 42.0 199 1

WindowAgg (cost=755.94..902.36 rows=8,367 width=77) (actual time=0.663..0.776 rows=199 loops=1)

7. 0.363 0.660 ↑ 42.0 199 1

Sort (cost=755.94..776.86 rows=8,367 width=47) (actual time=0.652..0.660 rows=199 loops=1)

  • Sort Key: obj_usergrp_map.object_id
  • Sort Method: quicksort Memory: 39kB
8. 0.050 0.297 ↑ 42.0 199 1

Nested Loop (cost=31.96..210.81 rows=8,367 width=47) (actual time=0.080..0.297 rows=199 loops=1)

9. 0.013 0.061 ↓ 1.1 31 1

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

  • Group Key: adm_grp_usr_map.group_id
10. 0.024 0.048 ↓ 1.0 31 1

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

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=9
11. 0.024 0.024 ↓ 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.024..0.024 rows=31 loops=1)

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

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

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
13. 0.059 284.079 ↓ 5.9 59 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
14. 14.416 284.020 ↓ 5.9 59 1

Nested Loop (cost=18,604.60..18,920.64 rows=10 width=1,983) (actual time=254.125..284.020 rows=59 loops=1)

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 103545
15. 2.433 258.394 ↓ 59.0 59 1

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

  • Join Filter: ((named_selections_summ_tbl.object_id)::text = (tm.object_id)::text)
  • Rows Removed by Join Filter: 32155
16. 0.048 254.073 ↓ 59.0 59 1

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

17. 0.013 253.612 ↓ 59.0 59 1

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

18. 0.190 252.832 ↓ 59.0 59 1

Merge Left Join (cost=18,603.91..18,604.17 rows=1 width=1,920) (actual time=252.593..252.832 rows=59 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (oum.object_id)::text)
19. 0.185 248.578 ↓ 59.0 59 1

Sort (cost=18,553.67..18,553.68 rows=1 width=1,888) (actual time=248.566..248.578 rows=59 loops=1)

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 40kB
20. 1.017 248.393 ↓ 59.0 59 1

Nested Loop (cost=18,535.02..18,553.66 rows=1 width=1,888) (actual time=184.120..248.393 rows=59 loops=1)

21. 56.093 187.220 ↓ 477.4 3,342 1

HashAggregate (cost=18,534.60..18,534.67 rows=7 width=2,226) (actual time=183.841..187.220 rows=3,342 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.497 131.127 ↓ 513.0 3,591 1

Append (cost=0.00..18,534.27 rows=7 width=2,226) (actual time=0.025..131.127 rows=3,591 loops=1)

23. 53.049 53.049 ↓ 2,737.0 2,737 1

Seq Scan on named_selections_summ_tbl (cost=0.00..6,023.94 rows=1 width=960) (actual time=0.025..53.049 rows=2,737 loops=1)

  • Filter: ((object_type = '7'::numeric) AND (lower((org_name)::text) = 'cl1'::text) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 32594
24. 38.253 38.253 ↓ 150.8 754 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=0.115..38.253 rows=754 loops=1)

  • Filter: ((object_type = '7'::numeric) AND (scope = ANY ('{1,3,5}'::numeric[])) AND (lower((org_name)::text) = 'cl1'::text))
  • Rows Removed by Filter: 34577
25. 0.149 39.328 ↓ 100.0 100 1

Hash Join (cost=6,339.74..6,530.11 rows=1 width=960) (actual time=39.035..39.328 rows=100 loops=1)

  • Hash Cond: ((obj_user_map.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
26. 0.429 1.069 ↑ 12.4 678 1

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

  • Group Key: obj_user_map.object_id
27. 0.052 0.640 ↑ 11.6 727 1

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

28. 0.244 0.288 ↓ 11.2 528 1

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

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=55
29. 0.044 0.044 ↓ 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.044..0.044 rows=532 loops=1)

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

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

31. 0.024 0.066 ↓ 1.1 31 1

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

  • Group Key: adm_grp_usr_map_1.group_id
32. 0.025 0.042 ↓ 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.021..0.042 rows=31 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=9
33. 0.017 0.017 ↓ 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.017..0.017 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.317 38.110 ↓ 255.0 255 1

Hash (cost=5,936.28..5,936.28 rows=1 width=960) (actual time=38.109..38.110 rows=255 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 210kB
36. 37.793 37.793 ↓ 255.0 255 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=0.155..37.793 rows=255 loops=1)

  • Filter: ((object_type = '7'::numeric) AND (scope = '2'::numeric) AND (lower((org_name)::text) = 'cl1'::text))
  • Rows Removed by Filter: 35076
37. 60.156 60.156 ↓ 0.0 0 3,342

Index Scan using folder_obj_map_pkey on folder_obj_map fo (cost=0.42..2.70 rows=1 width=43) (actual time=0.018..0.018 rows=0 loops=3,342)

  • 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[]))
  • Rows Removed by Filter: 1
38. 1.453 4.064 ↓ 10.1 473 1

Sort (cost=50.24..50.36 rows=47 width=75) (actual time=4.021..4.064 rows=473 loops=1)

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 64kB
39. 0.038 2.611 ↓ 11.1 522 1

Subquery Scan on oum (cost=47.99..48.93 rows=47 width=75) (actual time=2.516..2.611 rows=522 loops=1)

40. 0.283 2.573 ↓ 11.1 522 1

HashAggregate (cost=47.99..48.46 rows=47 width=293) (actual time=2.515..2.573 rows=522 loops=1)

  • Group Key: max(obj_user_map_1.is_editable) OVER (?), obj_user_map_1.object_id, obj_user_map_1.user_id
41. 0.306 2.290 ↓ 11.2 528 1

WindowAgg (cost=46.70..47.64 rows=47 width=293) (actual time=1.977..2.290 rows=528 loops=1)

42. 1.647 1.984 ↓ 11.2 528 1

Sort (cost=46.70..46.82 rows=47 width=263) (actual time=1.961..1.984 rows=528 loops=1)

  • Sort Key: obj_user_map_1.user_id, obj_user_map_1.object_id
  • Sort Method: quicksort Memory: 96kB
43. 0.286 0.337 ↓ 11.2 528 1

Bitmap Heap Scan on obj_user_map obj_user_map_1 (cost=1.75..45.40 rows=47 width=263) (actual time=0.063..0.337 rows=528 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=55
44. 0.051 0.051 ↓ 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.051..0.051 rows=532 loops=1)

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

Index Only Scan using inx_objftrmaptbl_objidftrname on obj_filter_map_tbl ofmt (cost=0.41..2.15 rows=1 width=79) (actual time=0.013..0.013 rows=0 loops=59)

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

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl ft (cost=0.28..2.18 rows=1 width=43) (actual time=0.007..0.007 rows=0 loops=59)

  • Index Cond: (((named_selections_summ_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
47. 1.888 1.888 ↓ 1.0 545 59

Seq Scan on tag_master tm (cost=0.00..11.43 rows=543 width=50) (actual time=0.001..0.032 rows=545 loops=59)

48. 11.210 11.210 ↑ 1.0 1,756 59

Seq Scan on cube_tbl cb (cost=0.00..267.56 rows=1,756 width=25) (actual time=0.001..0.190 rows=1,756 loops=59)

49. 1.317 2.508 ↓ 1.0 6,739 1

Hash (cost=143.35..143.35 rows=6,735 width=44) (actual time=2.508..2.508 rows=6,739 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 571kB
50. 1.191 1.191 ↓ 1.0 6,739 1

Seq Scan on object_collection_map objcolmap (cost=0.00..143.35 rows=6,735 width=44) (actual time=0.019..1.191 rows=6,739 loops=1)

Planning time : 5.509 ms
Execution time : 288.592 ms