explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SVj1

Settings
# exclusive inclusive rows x rows loops node
1. 0.332 296.686 ↓ 102.0 102 1

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

2.          

CTE temp_story

3. 0.941 112.683 ↓ 15.5 3,101 1

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

  • Group Key: story_tbl_1.story_id
4. 7.199 111.742 ↓ 8.4 3,101 1

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

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

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

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

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

7. 17.386 29.781 ↓ 3.6 18 1

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

  • Workers Planned: 8
  • Workers Launched: 8
8. 12.395 12.395 ↓ 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.488..12.395 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.001 18.826 ↓ 0.0 0 1

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

10. 0.001 18.825 ↓ 0.0 0 1

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

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

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

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

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

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

15. 0.747 35.905 ↓ 8.6 3,125 1

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

  • Group Key: vis_story_grp_map_tbl.story_id
16. 0.000 35.158 ↓ 8.7 3,155 1

Gather Merge (cost=3,697.00..3,742.50 rows=364 width=41) (actual time=31.117..35.158 rows=3,155 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
17. 1.120 85.808 ↓ 7.6 394 8

Partial GroupAggregate (cost=2,696.88..2,697.79 rows=52 width=41) (actual time=10.572..10.726 rows=394 loops=8)

  • Group Key: vis_story_grp_map_tbl.story_id
18. 6.392 84.688 ↓ 7.8 404 8

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

  • Sort Key: vis_story_grp_map_tbl.story_id
  • Sort Method: quicksort Memory: 65kB
  • Worker 0: Sort Method: quicksort Memory: 29kB
  • Worker 1: Sort Method: quicksort Memory: 29kB
  • Worker 2: Sort Method: quicksort Memory: 26kB
  • Worker 3: Sort Method: quicksort Memory: 49kB
  • Worker 4: Sort Method: quicksort Memory: 25kB
  • Worker 5: Sort Method: quicksort Memory: 61kB
  • Worker 6: Sort Method: quicksort Memory: 63kB
19. 3.008 78.296 ↓ 7.8 404 8

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

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
20. 73.952 73.952 ↓ 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.712..9.244 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.249 1.336 ↑ 54.5 2 8

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

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

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
23. 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)
24. 18.750 18.750 ↑ 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.006..0.006 rows=1 loops=3,125)

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

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

26. 1.051 295.600 ↓ 102.0 102 1

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

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

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

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

29. 20.738 226.684 ↓ 102.0 102 1

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

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

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

31. 0.032 181.580 ↓ 7.3 102 1

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

32. 0.147 181.548 ↓ 7.3 102 1

Sort (cost=8,466.70..8,466.73 rows=14 width=67) (actual time=181.533..181.548 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.057 181.401 ↓ 7.3 102 1

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

34. 0.011 179.188 ↓ 19.6 98 1

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

35. 0.042 179.177 ↓ 19.6 98 1

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

36. 0.008 179.135 ↓ 5.8 98 1

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

37. 0.011 149.443 ↓ 19.6 98 1

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

38. 0.190 149.432 ↓ 19.6 98 1

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

39. 0.118 149.242 ↑ 1.3 1,620 1

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

40. 0.014 146.143 ↓ 19.6 98 1

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

41. 0.114 146.129 ↓ 19.6 98 1

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

42. 1.492 115.005 ↓ 15.5 3,101 1

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

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

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

44. 31.010 31.010 ↓ 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.010..0.010 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.142 2.981 ↑ 1.4 1,522 1

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

46. 0.587 2.839 ↑ 1.4 1,522 1

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

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

48. 0.200 1.844 ↑ 1.0 1,522 1

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

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

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

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

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

51. 0.000 29.683 ↓ 0.0 0 1

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

52. 1.454 1.805 ↓ 15.5 3,101 1

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

  • Group Key: (temp_story_1.story_id)::text
53. 0.351 0.351 ↓ 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.351 rows=3,101 loops=1)

54. 27.909 27.909 ↓ 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.009..0.009 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. 2.156 2.156 ↑ 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.021..0.022 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.918 0.918 ↑ 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.009..0.009 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. 23.358 23.358 ↓ 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.229 rows=3,101 loops=102)

58. 0.714 0.714 ↓ 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.007..0.007 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 42.126 ↓ 0.0 0 102

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

  • Group Key: story_exec_info_tbl.story_id
60. 0.023 42.126 ↓ 0.0 0 102

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

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

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

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

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

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

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

65. 2.815 24.990 ↑ 7.1 163 102

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

  • Group Key: story_report_map.story_id
66. 1.978 22.175 ↑ 1.5 788 1

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

67. 1.273 1.591 ↓ 15.5 3,101 1

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

  • Group Key: (temp_story_3.story_id)::text
68. 0.318 0.318 ↓ 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.318 rows=3,101 loops=1)

69. 18.606 18.606 ↓ 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.006..0.006 rows=0 loops=3,101)

  • Index Cond: ((story_id)::text = (temp_story_3.story_id)::text)
70. 0.714 0.714 ↓ 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.007..0.007 rows=0 loops=102)

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