explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 28xX

Settings
# exclusive inclusive rows x rows loops node
1. 4.786 3,567.195 ↑ 1,240.6 356 1

HashAggregate (cost=261,970.71..268,595.38 rows=441,645 width=2,035) (actual time=3,563.364..3,567.195 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.272 3,562.409 ↑ 1,240.6 356 1

Merge Right Join (cost=218,108.42..239,888.46 rows=441,645 width=2,035) (actual time=3,437.546..3,562.409 rows=356 loops=1)

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

  • Heap Fetches: 159652
4. 0.133 3,432.769 ↑ 1,240.0 356 1

Materialize (cost=218,108.00..225,927.77 rows=441,442 width=2,029) (actual time=3,432.288..3,432.769 rows=356 loops=1)

5. 0.289 3,432.636 ↑ 1,240.0 356 1

Merge Left Join (cost=218,108.00..224,824.17 rows=441,442 width=2,029) (actual time=3,432.280..3,432.636 rows=356 loops=1)

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

Sort (cost=212,843.37..212,889.95 rows=18,634 width=1,997) (actual time=3,429.016..3,429.084 rows=356 loops=1)

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

Hash Right Join (cost=207,961.88..211,521.69 rows=18,634 width=1,997) (actual time=3,416.816..3,427.317 rows=356 loops=1)

  • Hash Cond: ((obj_usergrp_map.object_id)::text = (named_selections_summ_tbl.object_id)::text)
8. 114.272 3,189.909 ↓ 1.3 164,302 1

HashAggregate (cost=152,762.02..154,047.15 rows=128,513 width=76) (actual time=3,155.844..3,189.909 rows=164,302 loops=1)

  • Group Key: max(obj_usergrp_map.is_editable) OVER (?), obj_usergrp_map.object_id
9. 153.796 3,075.637 ↑ 3.7 263,382 1

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

10. 2,697.853 2,921.841 ↑ 3.7 263,382 1

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

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

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

12. 0.031 0.079 ↑ 35.1 16 1

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

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

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

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

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

  • Index Cond: (group_id = adm_grp_usr_map.group_id)
16. 0.144 219.804 ↓ 12.3 356 1

Hash (cost=55,199.50..55,199.50 rows=29 width=1,965) (actual time=219.804..219.804 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 97kB
17. 64.414 219.660 ↓ 12.3 356 1

Nested Loop (cost=53,675.92..55,199.50 rows=29 width=1,965) (actual time=195.269..219.660 rows=356 loops=1)

  • Join Filter: (named_selections_summ_tbl.cube_id = (cb.cube_id)::numeric)
  • Rows Removed by Join Filter: 412248
18. 1.099 1.099 ↑ 1.0 1,159 1

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

19. 16.336 154.147 ↓ 71.2 356 1,159

Materialize (cost=53,675.92..54,383.51 rows=5 width=1,942) (actual time=0.110..0.133 rows=356 loops=1,159)

20. 0.000 137.811 ↓ 71.2 356 1

Nested Loop Left Join (cost=53,675.92..54,383.49 rows=5 width=1,942) (actual time=127.375..137.811 rows=356 loops=1)

21. 0.107 134.281 ↓ 71.2 356 1

Nested Loop Left Join (cost=53,675.51..54,380.58 rows=5 width=1,935) (actual time=127.348..134.281 rows=356 loops=1)

22. 3.075 127.410 ↓ 71.2 356 1

Hash Right Join (cost=53,674.96..54,368.47 rows=5 width=1,892) (actual time=127.301..127.410 rows=356 loops=1)

  • Hash Cond: ((tm.object_id)::text = (named_selections_summ_tbl.object_id)::text)
23. 2.740 2.740 ↑ 1.0 28,761 1

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

24. 0.133 121.595 ↓ 71.2 356 1

Hash (cost=53,674.89..53,674.89 rows=5 width=1,888) (actual time=121.595..121.595 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
25. 0.240 121.462 ↓ 71.2 356 1

Hash Join (cost=53,534.19..53,674.89 rows=5 width=1,888) (actual time=120.508..121.462 rows=356 loops=1)

  • Hash Cond: ((named_selections_summ_tbl.object_id)::text = (fo.object_id)::text)
26. 4.889 120.494 ↑ 6.4 970 1

HashAggregate (cost=51,779.85..51,842.04 rows=6,219 width=2,352) (actual time=119.761..120.494 rows=970 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
27. 0.076 115.605 ↑ 6.4 973 1

Append (cost=0.43..51,453.36 rows=6,219 width=2,352) (actual time=7.551..115.605 rows=973 loops=1)

28. 37.989 37.989 ↑ 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=7.549..37.989 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
29. 1.137 1.137 ↑ 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.045..1.137 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[])))
30. 0.117 76.403 ↓ 0.0 0 1

Hash Join (cost=27,155.25..28,026.62 rows=7 width=1,092) (actual time=76.402..76.403 rows=0 loops=1)

  • Hash Cond: ((named_selections_summ_tbl_2.object_id)::text = (obj_user_map.object_id)::text)
31. 0.811 0.811 ↑ 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.025..0.811 rows=418 loops=1)

  • Index Cond: ((object_type = '7'::numeric) AND (lower((org_name)::text) = 'gsk_ld'::text) AND (scope = '2'::numeric))
32. 0.061 75.475 ↑ 56.4 172 1

Hash (cost=27,033.53..27,033.53 rows=9,704 width=118) (actual time=75.475..75.475 rows=172 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 141kB
33. 0.233 75.414 ↑ 56.4 172 1

HashAggregate (cost=26,839.45..26,936.49 rows=9,704 width=118) (actual time=75.302..75.414 rows=172 loops=1)

  • Group Key: obj_user_map.object_id
34. 0.000 75.181 ↑ 30.6 317 1

Append (cost=54.37..26,815.19 rows=9,704 width=118) (actual time=0.059..75.181 rows=317 loops=1)

35. 0.413 0.453 ↑ 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.059..0.453 rows=300 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
37. 19.416 74.957 ↑ 286.6 17 1

Gather (cost=1,000.42..22,217.56 rows=4,873 width=44) (actual time=0.753..74.957 rows=17 loops=1)

  • Workers Planned: 9
  • Workers Launched: 9
38. 0.085 55.541 ↑ 270.5 2 10

Nested Loop Semi Join (cost=0.42..20,730.26 rows=541 width=44) (actual time=37.796..55.541 rows=2 loops=10)

39. 55.444 55.444 ↑ 89.2 8 10

Parallel Seq Scan on obj_usergrp_map obj_usergrp_map_1 (cost=0.00..20,256.89 rows=714 width=52) (actual time=34.328..55.444 rows=8 loops=10)

  • Filter: (lower((last_upd_by)::text) = 'zicc232'::text)
  • Rows Removed by Filter: 128489
40. 0.012 0.012 ↓ 0.0 0 80

Index Scan using grp_usr_map_usrid_grpid_i on adm_grp_usr_map adm_grp_usr_map_1 (cost=0.42..0.71 rows=1 width=8) (actual time=0.011..0.012 rows=0 loops=80)

  • Index Cond: ((lower((user_id)::text) = 'zicc232'::text) AND (group_id = obj_usergrp_map_1.group_id))
41. 0.104 0.728 ↑ 7.5 360 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 60kB
42. 0.624 0.624 ↑ 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.024..0.624 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. 6.764 6.764 ↑ 1.0 1 356

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

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

  • Index Cond: (((named_selections_summ_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
45. 0.767 3.263 ↑ 28.0 169 1

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

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

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

47. 0.347 2.469 ↑ 28.0 169 1

HashAggregate (cost=4,880.53..4,927.92 rows=4,739 width=85) (actual time=2.429..2.469 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.239 2.122 ↑ 16.1 300 1

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

49. 1.251 1.883 ↑ 16.1 300 1

Sort (cost=4,747.68..4,759.75 rows=4,831 width=55) (actual time=1.869..1.883 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.525 0.632 ↑ 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.148..0.632 rows=300 loops=1)

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

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