explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5aft

Settings
# exclusive inclusive rows x rows loops node
1. 4.974 3,304.256 ↑ 1,240.6 356 1

HashAggregate (cost=313,547.06..320,171.74 rows=441,645 width=2,035) (actual time=3,300.164..3,304.256 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.is_editable) OVER (?)))::character varying, tm.tag_name, objcolmap.collection_id
2. 31.454 3,299.282 ↑ 1,240.6 356 1

Merge Right Join (cost=269,684.78..291,464.81 rows=441,645 width=2,035) (actual time=3,193.174..3,299.282 rows=356 loops=1)

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

  • Heap Fetches: 159652
4. 0.130 3,188.870 ↑ 1,240.0 356 1

Materialize (cost=269,684.36..277,504.13 rows=441,442 width=2,029) (actual time=3,188.408..3,188.870 rows=356 loops=1)

5. 0.288 3,188.740 ↑ 1,240.0 356 1

Merge Left Join (cost=269,684.36..276,400.52 rows=441,442 width=2,029) (actual time=3,188.404..3,188.740 rows=356 loops=1)

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

Sort (cost=264,419.72..264,466.31 rows=18,634 width=1,997) (actual time=3,185.734..3,185.789 rows=356 loops=1)

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

Hash Right Join (cost=259,538.24..263,098.05 rows=18,634 width=1,997) (actual time=3,176.065..3,184.222 rows=356 loops=1)

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (named_selections_summ_tbl.object_id)::text)
8. 100.573 2,724.998 ↓ 1.3 164,302 1

HashAggregate (cost=152,762.02..154,047.15 rows=128,513 width=76) (actual time=2,698.360..2,724.998 rows=164,302 loops=1)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
9. 135.106 2,624.425 ↑ 3.7 263,382 1

WindowAgg (cost=130,835.79..147,889.52 rows=974,499 width=76) (actual time=2,455.268..2,624.425 rows=263,382 loops=1)

10. 2,300.942 2,489.319 ↑ 3.7 263,382 1

Sort (cost=130,835.79..133,272.04 rows=974,499 width=46) (actual time=2,455.213..2,489.319 rows=263,382 loops=1)

  • Sort Key: obj_usergrp_map.object_id
  • Sort Method: quicksort Memory: 39431kB
11. 38.502 188.377 ↑ 3.7 263,382 1

Nested Loop (cost=540.94..33,900.91 rows=974,499 width=46) (actual time=0.092..188.377 rows=263,382 loops=1)

12. 0.031 0.083 ↑ 35.1 16 1

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

  • Group Key: adm_grp_usr_map.group_id
13. 0.027 0.052 ↑ 40.8 16 1

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

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

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

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

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
16. 0.191 443.356 ↓ 12.3 356 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 97kB
17. 49.907 443.165 ↓ 12.3 356 1

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

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 412248
18. 0.072 377.238 ↓ 71.2 356 1

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

19. 0.107 374.318 ↓ 71.2 356 1

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

20. 0.890 368.515 ↓ 71.2 356 1

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

  • Merge Cond: ((named_selections_summ_tbl.object_id)::text = (fo.object_id)::text)
21. 4.561 365.687 ↑ 7.2 970 1

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

  • Sort Key: named_selections_summ_tbl.object_id
  • Sort Method: quicksort Memory: 284kB
22. 0.332 361.126 ↑ 7.2 971 1

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

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (tm.object_id)::text)
23. 4.867 352.279 ↑ 7.1 971 1

HashAggregate (cost=102,326.89..102,395.60 rows=6,871 width=2,352) (actual time=351.566..352.279 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
24. 0.061 347.412 ↑ 7.1 974 1

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

25. 36.061 36.061 ↑ 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=6.716..36.061 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
26. 0.851 0.851 ↑ 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.039..0.851 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[])))
27. 15.052 310.439 ↑ 659.0 1 1

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

  • Hash Cond: ((obj_user_map.object_id)::text = (named_selections_summ_tbl_2.object_id)::text)
28. 108.184 294.649 ↑ 6.0 164,435 1

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

  • Group Key: obj_user_map.object_id
29. 14.104 186.465 ↑ 3.7 263,682 1

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

30. 0.363 0.397 ↑ 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.050..0.397 rows=300 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
32. 23.151 171.964 ↑ 3.7 263,382 1

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

33. 0.023 0.077 ↑ 35.1 16 1

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

  • Group Key: adm_grp_usr_map_1.group_id
34. 0.026 0.054 ↑ 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.032..0.054 rows=16 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
36. 148.736 148.736 ↓ 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=52) (actual time=0.011..9.296 rows=16,461 loops=16)

  • Index Cond: (group_id = adm_grp_usr_map_1.group_id)
37. 0.383 0.738 ↑ 2.2 418 1

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

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

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

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

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

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

41. 1.396 1.938 ↑ 7.5 360 1

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

  • Sort Key: fo.object_id
  • Sort Method: quicksort Memory: 53kB
42. 0.542 0.542 ↑ 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.031..0.542 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. 5.696 5.696 ↑ 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.016..0.016 rows=1 loops=356)

  • Index Cond: (object_id = (named_selections_summ_tbl.object_id)::text)
  • Heap Fetches: 356
44. 2.848 2.848 ↓ 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.008..0.008 rows=0 loops=356)

  • Index Cond: (((named_selections_summ_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
45. 14.633 16.020 ↑ 1.0 1,159 356

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

46. 1.387 1.387 ↑ 1.0 1,159 1

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

47. 0.726 2.663 ↑ 28.0 169 1

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

  • Sort Key: oum.object_id
  • Sort Method: quicksort Memory: 43kB
48. 0.017 1.937 ↑ 28.0 169 1

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

49. 0.188 1.920 ↑ 28.0 169 1

HashAggregate (cost=4,880.53..4,927.92 rows=4,739 width=85) (actual time=1.870..1.920 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
50. 0.158 1.732 ↑ 16.1 300 1

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

51. 1.082 1.574 ↑ 16.1 300 1

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

  • Sort Key: obj_user_map_1.user_id, obj_user_map_1.object_id
  • Sort Method: quicksort Memory: 67kB
52. 0.419 0.492 ↑ 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.098..0.492 rows=300 loops=1)

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

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