explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JggU

Settings
# exclusive inclusive rows x rows loops node
1. 0.296 244.858 ↓ 102.0 102 1

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

2.          

CTE temp_story

3. 0.783 92.975 ↓ 15.5 3,101 1

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

  • Group Key: story_tbl_1.story_id
4. 5.773 92.192 ↓ 8.4 3,101 1

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

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

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

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

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

7. 16.341 27.114 ↓ 3.6 18 1

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

  • Workers Planned: 8
  • Workers Launched: 8
8. 10.773 10.773 ↓ 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=5.321..10.773 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 15.627 ↓ 0.0 0 1

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

10. 0.001 15.627 ↓ 0.0 0 1

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

  • Group Key: vis_story_user_map_tbl.story_id
11. 0.010 15.626 ↓ 0.0 0 1

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

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

Seq Scan on vis_story_user_map_tbl (cost=0.00..1,571.01 rows=1 width=9) (actual time=15.616..15.616 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.918 42.405 ↓ 8.5 3,083 1

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

15. 0.629 27.987 ↓ 8.6 3,125 1

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

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

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

  • Workers Planned: 7
  • Workers Launched: 7
17. 0.880 71.416 ↓ 7.6 396 8

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

  • Group Key: vis_story_grp_map_tbl.story_id
18. 5.136 70.536 ↓ 7.8 404 8

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

  • Sort Key: vis_story_grp_map_tbl.story_id
  • Sort Method: quicksort Memory: 64kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 58kB
  • Worker 2: Sort Method: quicksort Memory: 25kB
  • Worker 3: Sort Method: quicksort Memory: 25kB
  • Worker 4: Sort Method: quicksort Memory: 25kB
  • Worker 5: Sort Method: quicksort Memory: 64kB
  • Worker 6: Sort Method: quicksort Memory: 64kB
19. 2.144 65.400 ↓ 7.8 404 8

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

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
20. 61.504 61.504 ↓ 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=2.873..7.688 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. 1.686 1.752 ↑ 54.5 2 8

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

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
22. 0.038 0.066 ↑ 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.039..0.066 rows=16 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
23. 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)
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.019 244.562 ↓ 102.0 102 1

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

26. 0.949 243.931 ↓ 102.0 102 1

Nested Loop Left Join (cost=19,900.58..19,983.88 rows=1 width=221) (actual time=199.587..243.931 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 221.970 ↓ 102.0 102 1

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

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

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

29. 18.765 182.127 ↓ 102.0 102 1

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

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

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

31. 0.029 141.670 ↓ 7.3 102 1

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

32. 0.126 141.641 ↓ 7.3 102 1

Sort (cost=8,466.70..8,466.73 rows=14 width=67) (actual time=141.626..141.641 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.000 141.515 ↓ 7.3 102 1

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

34. 0.009 140.451 ↓ 19.6 98 1

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

35. 0.028 140.442 ↓ 19.6 98 1

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

36. 0.007 140.414 ↓ 5.8 98 1

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

37. 0.009 121.019 ↓ 19.6 98 1

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

38. 0.143 121.010 ↓ 19.6 98 1

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

39. 0.093 120.867 ↑ 1.3 1,620 1

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

40. 0.013 118.344 ↓ 19.6 98 1

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

41. 1.884 118.331 ↓ 19.6 98 1

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

42. 1.084 94.740 ↓ 15.5 3,101 1

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

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

CTE Scan on temp_story (cost=0.00..4.00 rows=200 width=118) (actual time=92.069..93.656 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.108 2.430 ↑ 1.4 1,522 1

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

46. 0.451 2.322 ↑ 1.4 1,522 1

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

  • Hash Cond: ((vm.desk_id)::text = (vd.desk_id)::text)
47. 0.326 0.326 ↓ 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.011..0.326 rows=3,845 loops=1)

48. 0.161 1.545 ↑ 1.0 1,522 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
49. 1.384 1.384 ↑ 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.384 rows=1,522 loops=1)

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

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

51. 0.000 19.387 ↓ 0.0 0 1

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

52. 1.022 1.298 ↓ 15.5 3,101 1

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

  • Group Key: (temp_story_1.story_id)::text
53. 0.276 0.276 ↓ 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.001..0.276 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. 1.078 1.078 ↑ 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.010..0.011 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.808 20.808 ↓ 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.204 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 39.270 ↓ 0.0 0 102

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

  • Group Key: story_exec_info_tbl.story_id
60. 0.054 39.270 ↓ 0.0 0 102

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

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

Nested Loop Semi Join (cost=1,000.00..9,786.39 rows=1 width=6) (actual time=39.216..39.216 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. 16.243 38.874 ↑ 1.0 1 1

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

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

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

  • Filter: ((executed_time <= CURRENT_TIMESTAMP) AND (executed_time >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 55232
64. 0.258 0.258 ↓ 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.001..0.258 rows=3,101 loops=1)

65. 2.511 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.514 18.501 ↑ 1.5 788 1

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

67. 1.189 1.482 ↓ 15.5 3,101 1

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

  • Group Key: (temp_story_3.story_id)::text
68. 0.293 0.293 ↓ 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.293 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.747 ms
Execution time : 245.702 ms