explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GQyM

Settings
# exclusive inclusive rows x rows loops node
1. 7.233 3,596.119 ↑ 1,240.6 356 1

HashAggregate (cost=402,961.59..409,586.26 rows=441,645 width=2,035) (actual time=3,589.990..3,596.119 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(ogm.is_editable) OVER (?)))::character varying, tm.tag_name, objcolmap.collection_id
2. 33.697 3,588.886 ↑ 1,240.6 356 1

Merge Right Join (cost=359,099.30..380,879.34 rows=441,645 width=2,035) (actual time=3,467.698..3,588.886 rows=356 loops=1)

  • Merge Cond: ((objcolmap.obj_id)::text = (named_selections_summ_tbl.object_id)::text)
3. 91.754 91.754 ↑ 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.029..91.754 rows=159,652 loops=1)

  • Heap Fetches: 159652
4. 0.142 3,463.435 ↑ 1,240.0 356 1

Materialize (cost=359,098.88..366,918.65 rows=441,442 width=2,029) (actual time=3,462.938..3,463.435 rows=356 loops=1)

5. 0.307 3,463.293 ↑ 1,240.0 356 1

Merge Left Join (cost=359,098.88..365,815.05 rows=441,442 width=2,029) (actual time=3,462.927..3,463.293 rows=356 loops=1)

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (oum.object_id)::text)
6. 1.531 3,460.383 ↑ 52.3 356 1

Sort (cost=353,834.25..353,880.83 rows=18,634 width=1,997) (actual time=3,460.317..3,460.383 rows=356 loops=1)

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 154kB
7. 14.053 3,458.852 ↑ 52.3 356 1

Hash Right Join (cost=348,952.77..352,512.57 rows=18,634 width=1,997) (actual time=3,449.379..3,458.852 rows=356 loops=1)

  • Hash Cond: ((ogm.object_id)::text = (named_selections_summ_tbl.object_id)::text)
8. 112.795 2,946.433 ↓ 1.3 164,302 1

HashAggregate (cost=242,176.54..243,461.67 rows=128,513 width=120) (actual time=2,911.094..2,946.433 rows=164,302 loops=1)

  • Group Key: max(ogm.is_editable) OVER (?), ogm.object_id
9. 136.124 2,833.638 ↑ 6.2 263,382 1

WindowAgg (cost=205,326.58..233,987.66 rows=1,637,776 width=120) (actual time=2,660.297..2,833.638 rows=263,382 loops=1)

10. 2,517.422 2,697.514 ↑ 6.2 263,382 1

Sort (cost=205,326.58..209,421.02 rows=1,637,776 width=46) (actual time=2,660.258..2,697.514 rows=263,382 loops=1)

  • Sort Key: ogm.object_id
  • Sort Method: quicksort Memory: 39431kB
11. 37.138 180.092 ↑ 6.2 263,382 1

Nested Loop (cost=0.84..36,281.02 rows=1,637,776 width=46) (actual time=0.068..180.092 rows=263,382 loops=1)

12. 0.074 0.074 ↑ 40.8 16 1

Index Scan using grp_usr_map_usrid_grpid_i on adm_grp_usr_map admp (cost=0.42..569.53 rows=652 width=8) (actual time=0.029..0.074 rows=16 loops=1)

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

Index Scan using idx705 on obj_usergrp_map ogm (cost=0.43..46.52 rows=825 width=54) (actual time=0.012..8.930 rows=16,461 loops=16)

  • Index Cond: (group_id = admp.group_id)
14. 0.303 498.366 ↓ 12.3 356 1

Hash (cost=106,775.86..106,775.86 rows=29 width=1,965) (actual time=498.366..498.366 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 97kB
15. 64.971 498.063 ↓ 12.3 356 1

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

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 412248
16. 0.171 412.444 ↓ 71.2 356 1

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

17. 0.115 408.357 ↓ 71.2 356 1

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

18. 1.165 400.410 ↓ 71.2 356 1

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

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (fo.object_id)::text)
19. 5.602 396.896 ↑ 7.2 970 1

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

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 284kB
20. 0.388 391.294 ↑ 7.2 971 1

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

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (tm.object_id)::text)
21. 5.552 381.577 ↑ 7.1 971 1

HashAggregate (cost=102,326.89..102,395.60 rows=6,871 width=2,352) (actual time=380.392..381.577 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.072 376.025 ↑ 7.1 974 1

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

23. 41.415 41.415 ↑ 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=8.097..41.415 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
24. 1.339 1.339 ↑ 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.120..1.339 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[])))
25. 16.354 333.199 ↑ 659.0 1 1

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

  • Hash Cond: ((obj_user_map.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
26. 130.382 315.898 ↑ 6.0 164,435 1

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

  • Group Key: obj_user_map.object_id
27. 14.201 185.516 ↑ 3.7 263,682 1

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

28. 0.423 0.483 ↑ 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.078..0.483 rows=300 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
30. 22.888 170.832 ↑ 3.7 263,382 1

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

31. 0.036 0.104 ↑ 35.1 16 1

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

  • Group Key: adm_grp_usr_map.group_id
32. 0.030 0.068 ↑ 40.8 16 1

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

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
34. 147.840 147.840 ↓ 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.014..9.240 rows=16,461 loops=16)

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
35. 0.400 0.947 ↑ 2.2 418 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 401kB
36. 0.547 0.547 ↑ 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.029..0.547 rows=418 loops=1)

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

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

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

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

39. 1.676 2.349 ↑ 7.5 360 1

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

  • Sort Key: fo.object_id
  • Sort Method: quicksort Memory: 53kB
40. 0.673 0.673 ↑ 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.071..0.673 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[]))
41. 7.832 7.832 ↑ 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.022..0.022 rows=1 loops=356)

  • Index Cond: (object_id = (named_selections_summ_tbl.object_id)::text)
  • Heap Fetches: 356
42. 3.916 3.916 ↓ 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.011..0.011 rows=0 loops=356)

  • Index Cond: (((named_selections_summ_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
43. 19.085 20.648 ↑ 1.0 1,159 356

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

44. 1.563 1.563 ↑ 1.0 1,159 1

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

45. 0.654 2.603 ↑ 28.0 169 1

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

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 43kB
46. 0.022 1.949 ↑ 28.0 169 1

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

47. 0.190 1.927 ↑ 28.0 169 1

HashAggregate (cost=4,880.53..4,927.92 rows=4,739 width=85) (actual time=1.881..1.927 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
48. 0.153 1.737 ↑ 16.1 300 1

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

49. 1.037 1.584 ↑ 16.1 300 1

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

  • Sort Key: obj_user_map_1.user_id, obj_user_map_1.object_id
  • Sort Method: quicksort Memory: 67kB
50. 0.482 0.547 ↑ 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.090..0.547 rows=300 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
Planning time : 14.111 ms
Execution time : 3,659.010 ms