explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7zJ9

Settings
# exclusive inclusive rows x rows loops node
1. 0.284 237.076 ↓ 102.0 102 1

WindowAgg (cost=30,848.86..30,934.41 rows=1 width=283) (actual time=193.022..237.076 rows=102 loops=1)

2.          

CTE temp_story

3. 0.733 90.302 ↓ 15.5 3,101 1

GroupAggregate (cost=10,943.09..10,947.86 rows=200 width=150) (actual time=89.450..90.302 rows=3,101 loops=1)

  • Group Key: story_tbl_1.story_id
4. 5.502 89.569 ↓ 8.4 3,101 1

Sort (cost=10,943.09..10,944.01 rows=370 width=150) (actual time=89.446..89.569 rows=3,101 loops=1)

  • Sort Key: story_tbl_1.story_id
  • Sort Method: quicksort Memory: 242kB
5. 1.164 84.067 ↓ 8.4 3,101 1

HashAggregate (cost=10,919.91..10,923.61 rows=370 width=150) (actual time=83.761..84.067 rows=3,101 loops=1)

  • Group Key: story_tbl_1.story_id, ('Y'::bpchar)
6. 0.030 82.903 ↓ 8.4 3,101 1

Append (cost=1,000.00..10,918.06 rows=370 width=150) (actual time=6.365..82.903 rows=3,101 loops=1)

7. 14.749 25.936 ↓ 3.6 18 1

Gather (cost=1,000.00..4,737.17 rows=5 width=39) (actual time=6.365..25.936 rows=18 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
8. 11.187 11.187 ↓ 2.0 2 9

Parallel Seq Scan on story_tbl story_tbl_1 (cost=0.00..3,736.67 rows=1 width=39) (actual time=4.325..11.187 rows=2 loops=9)

  • Filter: ((type IS NULL) AND (guided_story_id IS NULL) AND (lower((org_name)::text) = 'gsk_ld'::text) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 21328
9. 0.000 14.985 ↓ 0.0 0 1

Nested Loop (cost=1,571.44..1,573.68 rows=1 width=39) (actual time=14.985..14.985 rows=0 loops=1)

10. 0.001 14.985 ↓ 0.0 0 1

GroupAggregate (cost=1,571.02..1,571.04 rows=1 width=39) (actual time=14.984..14.985 rows=0 loops=1)

  • Group Key: vis_story_user_map_tbl.story_id
11. 0.016 14.984 ↓ 0.0 0 1

Sort (cost=1,571.02..1,571.02 rows=1 width=9) (actual time=14.983..14.984 rows=0 loops=1)

  • Sort Key: vis_story_user_map_tbl.story_id
  • Sort Method: quicksort Memory: 25kB
12. 14.968 14.968 ↓ 0.0 0 1

Seq Scan on vis_story_user_map_tbl (cost=0.00..1,571.01 rows=1 width=9) (actual time=14.968..14.968 rows=0 loops=1)

  • Filter: (((story_type_id)::text = '2'::text) AND (lower((user_id)::text) = 'zicc232'::text) AND (lower((org_name)::text) = 'gsk_ld'::text))
  • Rows Removed by Filter: 45295
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using story_tbl_pkey on story_tbl sty (cost=0.42..2.64 rows=1 width=7) (never executed)

  • Index Cond: (story_id = (vis_story_user_map_tbl.story_id)::text)
  • Heap Fetches: 0
14. 1.578 41.952 ↓ 8.5 3,083 1

Nested Loop (cost=3,697.42..4,601.65 rows=364 width=39) (actual time=23.893..41.952 rows=3,083 loops=1)

15. 0.604 27.874 ↓ 8.6 3,125 1

Finalize GroupAggregate (cost=3,697.00..3,747.96 rows=364 width=41) (actual time=23.863..27.874 rows=3,125 loops=1)

  • Group Key: vis_story_grp_map_tbl.story_id
16. 0.000 27.270 ↓ 8.7 3,166 1

Gather Merge (cost=3,697.00..3,742.50 rows=364 width=41) (actual time=23.854..27.270 rows=3,166 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
17. 0.912 72.352 ↓ 7.6 396 8

Partial GroupAggregate (cost=2,696.88..2,697.79 rows=52 width=41) (actual time=8.921..9.044 rows=396 loops=8)

  • Group Key: vis_story_grp_map_tbl.story_id
18. 4.920 71.440 ↓ 7.8 404 8

Sort (cost=2,696.88..2,697.01 rows=52 width=11) (actual time=8.912..8.930 rows=404 loops=8)

  • Sort Key: vis_story_grp_map_tbl.story_id
  • Sort Method: quicksort Memory: 58kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 37kB
  • Worker 2: Sort Method: quicksort Memory: 29kB
  • Worker 3: Sort Method: quicksort Memory: 59kB
  • Worker 4: Sort Method: quicksort Memory: 35kB
  • Worker 5: Sort Method: quicksort Memory: 51kB
  • Worker 6: Sort Method: quicksort Memory: 53kB
19. 2.984 66.520 ↓ 7.8 404 8

Parallel Hash Semi Join (cost=532.09..2,695.40 rows=52 width=11) (actual time=2.173..8.315 rows=404 loops=8)

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
20. 62.560 62.560 ↓ 6.7 695 8

Parallel Seq Scan on vis_story_grp_map_tbl (cost=0.00..2,160.55 rows=104 width=16) (actual time=1.918..7.820 rows=695 loops=8)

  • Filter: (((story_type_id)::text = '2'::text) AND (lower((org_name)::text) = 'gsk_ld'::text))
  • Rows Removed by Filter: 17483
21. 0.913 0.976 ↑ 54.5 2 8

Parallel Hash (cost=530.73..530.73 rows=109 width=8) (actual time=0.122..0.122 rows=2 loops=8)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
22. 0.036 0.063 ↑ 6.8 16 1

Parallel Bitmap Heap Scan on adm_grp_usr_map (cost=8.77..530.73 rows=109 width=8) (actual time=0.037..0.063 rows=16 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
24. 12.500 12.500 ↑ 1.0 1 3,125

Index Only Scan using story_tbl_pkey on story_tbl sty_1 (cost=0.42..2.34 rows=1 width=7) (actual time=0.004..0.004 rows=1 loops=3,125)

  • Index Cond: (story_id = (vis_story_grp_map_tbl.story_id)::text)
  • Heap Fetches: 3083
25. 0.031 236.792 ↓ 102.0 102 1

Nested Loop Left Join (cost=19,901.00..19,986.52 rows=1 width=227) (actual time=192.527..236.792 rows=102 loops=1)

26. 0.949 236.149 ↓ 102.0 102 1

Nested Loop Left Join (cost=19,900.58..19,983.88 rows=1 width=221) (actual time=192.510..236.149 rows=102 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_report_map.story_id)::text)
  • Rows Removed by Join Filter: 16565
27. 0.000 214.188 ↓ 102.0 102 1

Nested Loop Left Join (cost=18,253.93..18,299.56 rows=1 width=204) (actual time=173.809..214.188 rows=102 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_exec_info_tbl.story_id)::text)
28. 0.070 178.185 ↓ 102.0 102 1

Nested Loop Left Join (cost=8,467.53..8,513.12 rows=1 width=196) (actual time=137.850..178.185 rows=102 loops=1)

29. 18.450 177.605 ↓ 102.0 102 1

Nested Loop (cost=8,467.12..8,510.48 rows=1 width=189) (actual time=137.834..177.605 rows=102 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (tmp.story_id)::text)
  • Rows Removed by Join Filter: 316200
30. 0.081 138.755 ↓ 102.0 102 1

Nested Loop (cost=8,467.12..8,503.98 rows=1 width=157) (actual time=137.831..138.755 rows=102 loops=1)

31. 0.028 137.858 ↓ 7.3 102 1

Unique (cost=8,466.70..8,466.84 rows=14 width=67) (actual time=137.814..137.858 rows=102 loops=1)

32. 0.121 137.830 ↓ 7.3 102 1

Sort (cost=8,466.70..8,466.73 rows=14 width=67) (actual time=137.814..137.830 rows=102 loops=1)

  • Sort Key: cmn_search_tbl.object_id, cmn_search_tbl.type, cmn_search_tbl.keyword
  • Sort Method: quicksort Memory: 32kB
33. 0.015 137.709 ↓ 7.3 102 1

Nested Loop (cost=5.62..8,466.43 rows=14 width=67) (actual time=136.711..137.709 rows=102 loops=1)

34. 0.009 136.714 ↓ 19.6 98 1

Subquery Scan on ANY_subquery (cost=5.06..8,368.18 rows=5 width=118) (actual time=136.695..136.714 rows=98 loops=1)

35. 0.027 136.705 ↓ 19.6 98 1

HashSetOp Except (cost=5.06..8,368.13 rows=5 width=122) (actual time=136.694..136.705 rows=98 loops=1)

36. 0.007 136.678 ↓ 5.8 98 1

Append (cost=5.06..8,368.09 rows=17 width=122) (actual time=117.480..136.678 rows=98 loops=1)

37. 0.008 117.492 ↓ 19.6 98 1

Result (cost=5.06..4,446.88 rows=5 width=122) (actual time=117.479..117.492 rows=98 loops=1)

38. 0.141 117.484 ↓ 19.6 98 1

HashSetOp Except (cost=5.06..4,446.83 rows=5 width=122) (actual time=117.478..117.484 rows=98 loops=1)

39. 0.085 117.343 ↑ 1.3 1,620 1

Append (cost=5.06..4,441.60 rows=2,094 width=122) (actual time=91.597..117.343 rows=1,620 loops=1)

40. 0.012 114.808 ↓ 19.6 98 1

Subquery Scan on *SELECT* 1 (cost=5.06..3,921.05 rows=5 width=45) (actual time=91.596..114.808 rows=98 loops=1)

41. 1.170 114.796 ↓ 19.6 98 1

Nested Loop (cost=5.06..3,921.00 rows=5 width=41) (actual time=91.596..114.796 rows=98 loops=1)

42. 0.988 91.919 ↓ 15.5 3,101 1

HashAggregate (cost=4.50..6.50 rows=200 width=118) (actual time=91.564..91.919 rows=3,101 loops=1)

  • Group Key: (temp_story.story_id)::text
43. 90.931 90.931 ↓ 15.5 3,101 1

CTE Scan on temp_story (cost=0.00..4.00 rows=200 width=118) (actual time=89.452..90.931 rows=3,101 loops=1)

44. 21.707 21.707 ↓ 0.0 0 3,101

Index Scan using cmn_search_idx1 on cmn_search_tbl cmn_search_tbl_1 (cost=0.56..19.56 rows=1 width=41) (actual time=0.007..0.007 rows=0 loops=3,101)

  • Index Cond: ((object_id)::text = (temp_story.story_id)::text)
  • Filter: ((lower((type)::text) = 'object type'::text) AND (lower((keyword)::text) = 'story'::text))
  • Rows Removed by Filter: 1
45. 0.104 2.450 ↑ 1.4 1,522 1

Subquery Scan on *SELECT* 2 (cost=416.88..510.08 rows=2,089 width=10) (actual time=1.616..2.450 rows=1,522 loops=1)

46. 0.423 2.346 ↑ 1.4 1,522 1

Hash Join (cost=416.88..489.19 rows=2,089 width=6) (actual time=1.615..2.346 rows=1,522 loops=1)

  • Hash Cond: ((vm.desk_id)::text = (vd.desk_id)::text)
47. 0.325 0.325 ↓ 1.0 3,845 1

Seq Scan on vis_desk_story_map vm (cost=0.00..62.26 rows=3,826 width=12) (actual time=0.009..0.325 rows=3,845 loops=1)

48. 0.168 1.598 ↑ 1.0 1,522 1

Hash (cost=397.85..397.85 rows=1,522 width=6) (actual time=1.598..1.598 rows=1,522 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
49. 1.430 1.430 ↑ 1.0 1,522 1

Seq Scan on vis_desk vd (cost=0.00..397.85 rows=1,522 width=6) (actual time=0.005..1.430 rows=1,522 loops=1)

  • Filter: ((target_app)::text = 'AE'::text)
  • Rows Removed by Filter: 1266
50. 0.001 19.179 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=5.06..3,921.12 rows=12 width=45) (actual time=19.179..19.179 rows=0 loops=1)

51. 0.000 19.178 ↓ 0.0 0 1

Nested Loop (cost=5.06..3,921.00 rows=12 width=41) (actual time=19.178..19.178 rows=0 loops=1)

52. 0.995 1.273 ↓ 15.5 3,101 1

HashAggregate (cost=4.50..6.50 rows=200 width=118) (actual time=0.924..1.273 rows=3,101 loops=1)

  • Group Key: (temp_story_1.story_id)::text
53. 0.278 0.278 ↓ 15.5 3,101 1

CTE Scan on temp_story temp_story_1 (cost=0.00..4.00 rows=200 width=118) (actual time=0.002..0.278 rows=3,101 loops=1)

54. 18.606 18.606 ↓ 0.0 0 3,101

Index Scan using cmn_search_idx1 on cmn_search_tbl cmn_search_tbl_2 (cost=0.56..19.56 rows=1 width=41) (actual time=0.006..0.006 rows=0 loops=3,101)

  • Index Cond: ((object_id)::text = (temp_story_1.story_id)::text)
  • Filter: ((lower((type)::text) = 'isdisplayinlibrary'::text) AND (lower((keyword)::text) = 'false'::text))
  • Rows Removed by Filter: 1
55. 0.980 0.980 ↑ 3.0 1 98

Index Scan using cmn_search_idx1 on cmn_search_tbl (cost=0.56..19.62 rows=3 width=67) (actual time=0.009..0.010 rows=1 loops=98)

  • Index Cond: ((object_id)::text = ("ANY_subquery".object_id)::text)
  • Filter: ((keyword IS NOT NULL) AND (lower((type)::text) = ANY ('{tags,"object type"}'::text[])))
  • Rows Removed by Filter: 20
56. 0.816 0.816 ↑ 1.0 1 102

Index Scan using story_tbl_pkey on story_tbl (cost=0.42..2.64 rows=1 width=90) (actual time=0.008..0.008 rows=1 loops=102)

  • Index Cond: ((story_id)::text = (cmn_search_tbl.object_id)::text)
  • Filter: ((type IS NULL) AND (guided_story_id IS NULL) AND (lower((org_name)::text) = 'gsk_ld'::text))
57. 20.400 20.400 ↓ 15.5 3,101 102

CTE Scan on temp_story tmp (cost=0.00..4.00 rows=200 width=150) (actual time=0.000..0.200 rows=3,101 loops=102)

58. 0.510 0.510 ↓ 0.0 0 102

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl fav (cost=0.41..2.63 rows=1 width=45) (actual time=0.005..0.005 rows=0 loops=102)

  • Index Cond: (((cmn_search_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
59. 0.000 36.006 ↓ 0.0 0 102

GroupAggregate (cost=9,786.40..9,786.42 rows=1 width=14) (actual time=0.353..0.353 rows=0 loops=102)

  • Group Key: story_exec_info_tbl.story_id
60. 0.058 36.006 ↓ 0.0 0 102

Sort (cost=9,786.40..9,786.41 rows=1 width=6) (actual time=0.353..0.353 rows=0 loops=102)

  • Sort Key: story_exec_info_tbl.story_id
  • Sort Method: quicksort Memory: 25kB
61. 0.092 35.948 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1,000.00..9,786.39 rows=1 width=6) (actual time=35.948..35.948 rows=0 loops=1)

  • Join Filter: ((story_exec_info_tbl.story_id)::text = (temp_story_2.story_id)::text)
  • Rows Removed by Join Filter: 3101
62. 13.944 35.652 ↑ 1.0 1 1

Gather (cost=1,000.00..9,779.89 rows=1 width=6) (actual time=35.536..35.652 rows=1 loops=1)

  • Workers Planned: 9
  • Workers Launched: 9
63. 21.708 21.708 ↓ 0.0 0 10

Parallel Seq Scan on story_exec_info_tbl (cost=0.00..8,779.79 rows=1 width=6) (actual time=21.708..21.708 rows=0 loops=10)

  • Filter: ((executed_time <= CURRENT_TIMESTAMP) AND (executed_time >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 55232
64. 0.204 0.204 ↓ 15.5 3,101 1

CTE Scan on temp_story temp_story_2 (cost=0.00..4.00 rows=200 width=118) (actual time=0.000..0.204 rows=3,101 loops=1)

65. 2.508 21.012 ↑ 7.1 163 102

HashAggregate (cost=1,646.64..1,658.23 rows=1,159 width=31) (actual time=0.183..0.206 rows=163 loops=102)

  • Group Key: story_report_map.story_id
66. 1.539 18.504 ↑ 1.5 788 1

Nested Loop (cost=4.92..1,635.05 rows=1,159 width=95) (actual time=1.066..18.504 rows=788 loops=1)

67. 1.184 1.460 ↓ 15.5 3,101 1

HashAggregate (cost=4.50..6.50 rows=200 width=118) (actual time=1.042..1.460 rows=3,101 loops=1)

  • Group Key: (temp_story_3.story_id)::text
68. 0.276 0.276 ↓ 15.5 3,101 1

CTE Scan on temp_story temp_story_3 (cost=0.00..4.00 rows=200 width=118) (actual time=0.001..0.276 rows=3,101 loops=1)

69. 15.505 15.505 ↓ 0.0 0 3,101

Index Scan using story_report_map_index on story_report_map (cost=0.42..8.08 rows=6 width=95) (actual time=0.005..0.005 rows=0 loops=3,101)

  • Index Cond: ((story_id)::text = (temp_story_3.story_id)::text)
70. 0.612 0.612 ↓ 0.0 0 102

Index Only Scan using inx_objcollmap_objid_collid on object_collection_map objcolmap (cost=0.42..2.64 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=102)

  • Index Cond: (obj_id = (cmn_search_tbl.object_id)::text)
  • Heap Fetches: 0
Planning time : 2.705 ms
Execution time : 237.900 ms