explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JrjS

Settings
# exclusive inclusive rows x rows loops node
1. 1.331 5,354.741 ↑ 9.7 102 1

Nested Loop Left Join (cost=174,572.15..39,504,542,755.78 rows=989 width=403) (actual time=364.742..5,354.741 rows=102 loops=1)

2.          

CTE temp_story

3. 0.901 52.275 ↓ 1.1 3,101 1

HashAggregate (cost=11,398.78..11,427.23 rows=2,845 width=118) (actual time=52.019..52.275 rows=3,101 loops=1)

  • Group Key: story_tbl_1.story_id
4. 0.000 51.374 ↓ 1.1 3,101 1

Append (cost=1,000.00..11,391.67 rows=2,845 width=118) (actual time=3.786..51.374 rows=3,101 loops=1)

5. 12.577 18.692 ↑ 2.6 18 1

Gather (cost=1,000.00..4,680.54 rows=46 width=7) (actual time=3.786..18.692 rows=18 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
6. 6.115 6.115 ↑ 3.0 2 9

Parallel Seq Scan on story_tbl story_tbl_1 (cost=0.00..3,675.94 rows=6 width=7) (actual time=3.153..6.115 rows=2 loops=9)

  • Filter: ((type IS NULL) AND (guided_story_id IS NULL) AND ((org_name)::text = 'GSK_LD'::text) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 21328
7. 1.287 32.743 ↓ 1.1 3,083 1

Nested Loop (cost=4,692.03..6,668.45 rows=2,799 width=7) (actual time=18.438..32.743 rows=3,083 loops=1)

8. 0.770 18.956 ↓ 1.1 3,125 1

HashAggregate (cost=4,691.61..4,719.60 rows=2,799 width=118) (actual time=18.412..18.956 rows=3,125 loops=1)

  • Group Key: vis_story_grp_map_tbl.story_id
9. 13.824 18.186 ↓ 1.2 3,230 1

Gather (cost=1,000.00..4,684.61 rows=2,799 width=118) (actual time=12.085..18.186 rows=3,230 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
10. 0.000 4.362 ↑ 6.9 404 8

Parallel Append (cost=0.00..3,404.71 rows=2,797 width=118) (actual time=2.242..4.362 rows=404 loops=8)

11. 0.284 4.548 ↓ 1.4 538 6

Parallel Hash Semi Join (cost=532.09..2,661.75 rows=397 width=9) (actual time=1.757..4.548 rows=538 loops=6)

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
12. 3.859 3.859 ↓ 1.2 927 6

Parallel Seq Scan on vis_story_grp_map_tbl (cost=0.00..2,108.62 rows=795 width=14) (actual time=1.091..3.859 rows=927 loops=6)

  • Filter: (((org_name)::text = 'GSK_LD'::text) AND ((story_type_id)::text = '2'::text))
  • Rows Removed by Filter: 23310
13. 0.339 0.405 ↑ 36.3 3 6

Parallel Hash (cost=530.73..530.73 rows=109 width=8) (actual time=0.405..0.405 rows=3 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
14. 0.035 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.043..0.066 rows=16 loops=1)

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

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
16. 7.385 7.385 ↓ 0.0 0 1

Parallel Seq Scan on vis_story_user_map_tbl (cost=0.00..700.98 rows=3 width=7) (actual time=7.385..7.385 rows=0 loops=1)

  • Filter: (((org_name)::text = 'GSK_LD'::text) AND ((story_type_id)::text = '2'::text) AND (lower((user_id)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 45295
17. 12.500 12.500 ↑ 1.0 1 3,125

Index Only Scan using story_tbl_pkey on story_tbl story_tbl_2 (cost=0.42..0.69 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: 3
18. 0.083 462.102 ↑ 9.7 102 1

Merge Left Join (cost=163,144.50..168,933.01 rows=989 width=285) (actual time=308.549..462.102 rows=102 loops=1)

  • Merge Cond: ((story_tbl.story_id)::text = (story_exec_info_tbl.story_id)::text)
19. 0.379 423.328 ↑ 9.7 102 1

Nested Loop Left Join (cost=153,290.93..159,075.91 rows=989 width=284) (actual time=269.852..423.328 rows=102 loops=1)

20. 0.073 421.929 ↑ 9.7 102 1

Merge Left Join (cost=153,290.51..158,595.52 rows=989 width=277) (actual time=269.825..421.929 rows=102 loops=1)

  • Merge Cond: ((cmn_search_tbl.object_id)::text = (vis_translation_story.story_id)::text)
21. 31.191 421.845 ↑ 9.7 102 1

Merge Left Join (cost=153,290.37..158,589.49 rows=989 width=213) (actual time=269.810..421.845 rows=102 loops=1)

  • Merge Cond: ((cmn_search_tbl.object_id)::text = (sty.story_id)::text)
22. 0.202 141.749 ↑ 9.7 102 1

Sort (cost=132,371.48..132,373.96 rows=989 width=181) (actual time=141.666..141.749 rows=102 loops=1)

  • Sort Key: cmn_search_tbl.object_id
  • Sort Method: quicksort Memory: 47kB
23. 0.055 141.547 ↑ 9.7 102 1

Hash Right Join (cost=129,882.32..132,322.28 rows=989 width=181) (actual time=141.031..141.547 rows=102 loops=1)

  • Hash Cond: ((story_report_map.story_id)::text = (story_tbl.story_id)::text)
24. 0.677 17.136 ↑ 468.1 219 1

HashAggregate (cost=4,665.23..5,690.36 rows=102,513 width=31) (actual time=16.636..17.136 rows=219 loops=1)

  • Group Key: story_report_map.story_id
25. 0.000 16.459 ↑ 377.0 788 1

Nested Loop (cost=64.44..1,694.57 rows=297,066 width=95) (actual time=0.921..16.459 rows=788 loops=1)

26. 0.980 1.245 ↓ 15.5 3,101 1

HashAggregate (cost=64.01..66.01 rows=200 width=118) (actual time=0.901..1.245 rows=3,101 loops=1)

  • Group Key: (temp_story.story_id)::text
27. 0.265 0.265 ↓ 1.1 3,101 1

CTE Scan on temp_story (cost=0.00..56.90 rows=2,845 width=118) (actual time=0.001..0.265 rows=3,101 loops=1)

28. 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.004..0.005 rows=0 loops=3,101)

  • Index Cond: ((story_id)::text = (temp_story.story_id)::text)
29. 0.033 124.356 ↑ 9.7 102 1

Hash (cost=125,204.73..125,204.73 rows=989 width=157) (actual time=124.356..124.356 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
30. 0.073 124.323 ↑ 9.7 102 1

Hash Join (cost=124,733.04..125,204.73 rows=989 width=157) (actual time=124.161..124.323 rows=102 loops=1)

  • Hash Cond: ((cmn_search_tbl.object_id)::text = (story_tbl.story_id)::text)
31. 0.165 101.069 ↑ 204.4 102 1

HashAggregate (cost=119,153.00..119,361.48 rows=20,848 width=67) (actual time=100.945..101.069 rows=102 loops=1)

  • Group Key: cmn_search_tbl.object_id, cmn_search_tbl.type, cmn_search_tbl.keyword
32. 0.076 100.904 ↑ 204.4 102 1

Nested Loop (cost=65.13..118,996.64 rows=20,848 width=67) (actual time=99.770..100.904 rows=102 loops=1)

33. 0.010 99.848 ↑ 81.7 98 1

Subquery Scan on ANY_subquery (cost=64.57..8,302.51 rows=8,010 width=118) (actual time=99.755..99.848 rows=98 loops=1)

34. 0.107 99.838 ↑ 81.7 98 1

HashSetOp Except (cost=64.57..8,222.41 rows=8,010 width=122) (actual time=99.754..99.838 rows=98 loops=1)

35. 0.006 99.731 ↑ 283.4 98 1

Append (cost=64.57..8,152.97 rows=27,774 width=122) (actual time=79.186..99.731 rows=98 loops=1)

36. 0.009 79.251 ↑ 81.7 98 1

Result (cost=64.57..4,281.45 rows=8,010 width=122) (actual time=79.185..79.251 rows=98 loops=1)

37. 0.290 79.242 ↑ 81.7 98 1

HashSetOp Except (cost=64.57..4,201.35 rows=8,010 width=122) (actual time=79.184..79.242 rows=98 loops=1)

38. 0.085 78.952 ↑ 6.2 1,620 1

Append (cost=64.57..4,176.08 rows=10,109 width=122) (actual time=53.512..78.952 rows=1,620 loops=1)

39. 0.013 76.800 ↑ 81.7 98 1

Subquery Scan on *SELECT* 1 (cost=64.57..3,615.11 rows=8,010 width=45) (actual time=53.512..76.800 rows=98 loops=1)

40. 1.140 76.787 ↑ 81.7 98 1

Nested Loop (cost=64.57..3,535.01 rows=8,010 width=41) (actual time=53.511..76.787 rows=98 loops=1)

41. 1.070 53.940 ↓ 15.5 3,101 1

HashAggregate (cost=64.01..66.01 rows=200 width=118) (actual time=53.475..53.940 rows=3,101 loops=1)

  • Group Key: (temp_story_1.story_id)::text
42. 52.870 52.870 ↓ 1.1 3,101 1

CTE Scan on temp_story temp_story_1 (cost=0.00..56.90 rows=2,845 width=118) (actual time=52.022..52.870 rows=3,101 loops=1)

43. 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..17.33 rows=1 width=41) (actual time=0.007..0.007 rows=0 loops=3,101)

  • Index Cond: ((object_id)::text = (temp_story_1.story_id)::text)
  • Filter: ((lower((type)::text) = 'object type'::text) AND (lower((keyword)::text) = 'story'::text))
  • Rows Removed by Filter: 1
44. 0.105 2.067 ↑ 1.4 1,522 1

Subquery Scan on *SELECT* 2 (cost=416.88..510.42 rows=2,099 width=10) (actual time=1.236..2.067 rows=1,522 loops=1)

45. 0.511 1.962 ↑ 1.4 1,522 1

Hash Join (cost=416.88..489.43 rows=2,099 width=6) (actual time=1.235..1.962 rows=1,522 loops=1)

  • Hash Cond: ((vm.desk_id)::text = (vd.desk_id)::text)
46. 0.232 0.232 ↑ 1.0 3,845 1

Seq Scan on vis_desk_story_map vm (cost=0.00..62.45 rows=3,845 width=12) (actual time=0.006..0.232 rows=3,845 loops=1)

47. 0.159 1.219 ↑ 1.0 1,522 1

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

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

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

Subquery Scan on *SELECT* 3 (cost=64.57..3,732.65 rows=19,764 width=45) (actual time=20.473..20.474 rows=0 loops=1)

50. 0.621 20.473 ↓ 0.0 0 1

Nested Loop (cost=64.57..3,535.01 rows=19,764 width=41) (actual time=20.473..20.473 rows=0 loops=1)

51. 0.977 1.246 ↓ 15.5 3,101 1

HashAggregate (cost=64.01..66.01 rows=200 width=118) (actual time=0.885..1.246 rows=3,101 loops=1)

  • Group Key: (temp_story_2.story_id)::text
52. 0.269 0.269 ↓ 1.1 3,101 1

CTE Scan on temp_story temp_story_2 (cost=0.00..56.90 rows=2,845 width=118) (actual time=0.001..0.269 rows=3,101 loops=1)

53. 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..17.33 rows=1 width=41) (actual time=0.006..0.006 rows=0 loops=3,101)

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

Index Scan using cmn_search_idx1 on cmn_search_tbl (cost=0.56..13.79 rows=3 width=67) (actual time=0.010..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
55. 1.838 23.181 ↓ 1.1 9,638 1

Hash (cost=5,466.26..5,466.26 rows=9,103 width=90) (actual time=23.181..23.181 rows=9,638 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1108kB
56. 15.137 21.343 ↓ 1.1 9,638 1

Gather (cost=1,000.00..5,466.26 rows=9,103 width=90) (actual time=0.552..21.343 rows=9,638 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
57. 6.206 6.206 ↑ 1.1 1,071 9

Parallel Seq Scan on story_tbl (cost=0.00..3,555.96 rows=1,138 width=90) (actual time=0.040..6.206 rows=1,071 loops=9)

  • Filter: ((type IS NULL) AND (guided_story_id IS NULL) AND ((org_name)::text = 'GSK_LD'::text))
  • Rows Removed by Filter: 20260
58. 110.784 248.905 ↓ 949.5 189,902 1

GroupAggregate (cost=20,918.88..26,200.67 rows=200 width=39) (actual time=101.608..248.905 rows=189,902 loops=1)

  • Group Key: sty.story_id
59. 26.900 138.121 ↑ 1.0 189,903 1

Unique (cost=20,918.88..22,358.69 rows=191,974 width=9) (actual time=101.586..138.121 rows=189,903 loops=1)

60. 77.542 111.221 ↑ 1.0 189,903 1

Sort (cost=20,918.88..21,398.82 rows=191,974 width=9) (actual time=101.584..111.221 rows=189,903 loops=1)

  • Sort Key: sty.story_id, st.locale_id
  • Sort Method: quicksort Memory: 15143kB
61. 14.590 33.679 ↑ 1.0 191,974 1

Merge Left Join (cost=0.56..4,072.63 rows=191,974 width=9) (actual time=0.040..33.679 rows=191,974 loops=1)

  • Merge Cond: ((sty.story_id)::text = (st.story_id)::text)
62. 19.080 19.080 ↑ 1.0 191,974 1

Index Only Scan using story_tbl_pkey on story_tbl sty (cost=0.42..3,576.33 rows=191,974 width=7) (actual time=0.029..19.080 rows=191,974 loops=1)

  • Heap Fetches: 495
63. 0.009 0.009 ↓ 0.0 0 1

Index Only Scan using vis_translation_story_idx on vis_translation_story st (cost=0.14..14.49 rows=150 width=120) (actual time=0.008..0.009 rows=0 loops=1)

  • Heap Fetches: 0
64. 0.001 0.011 ↓ 0.0 0 1

GroupAggregate (cost=0.14..3.50 rows=1 width=182) (actual time=0.011..0.011 rows=0 loops=1)

  • Group Key: vis_translation_story.story_id
65. 0.010 0.010 ↓ 0.0 0 1

Index Scan using vis_translation_story_idx on vis_translation_story (cost=0.14..3.48 rows=1 width=268) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (locale_id = '2'::smallint)
66. 1.020 1.020 ↓ 0.0 0 102

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

  • Index Cond: (((cmn_search_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
67. 0.001 38.691 ↓ 0.0 0 1

GroupAggregate (cost=9,853.57..9,854.60 rows=1 width=14) (actual time=38.691..38.691 rows=0 loops=1)

  • Group Key: story_exec_info_tbl.story_id
68. 0.002 38.690 ↓ 0.0 0 1

Merge Join (cost=9,853.57..9,854.59 rows=1 width=6) (actual time=38.689..38.690 rows=0 loops=1)

  • Merge Cond: ((story_exec_info_tbl.story_id)::text = (temp_story_3.story_id)::text)
69. 0.000 38.688 ↓ 0.0 0 1

Sort (cost=9,779.92..9,779.92 rows=1 width=6) (actual time=38.688..38.688 rows=0 loops=1)

  • Sort Key: story_exec_info_tbl.story_id
  • Sort Method: quicksort Memory: 25kB
70. 15.389 38.816 ↓ 0.0 0 1

Gather (cost=1,000.00..9,779.91 rows=1 width=6) (actual time=38.684..38.816 rows=0 loops=1)

  • Workers Planned: 9
  • Workers Launched: 9
71. 23.427 23.427 ↓ 0.0 0 10

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

  • Filter: ((executed_time <= CURRENT_TIMESTAMP) AND (executed_time >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 55232
72. 0.000 0.000 ↓ 0.0 0

Sort (cost=73.66..74.16 rows=200 width=118) (never executed)

  • Sort Key: temp_story_3.story_id
73. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=64.01..66.01 rows=200 width=118) (never executed)

  • Group Key: (temp_story_3.story_id)::text
74. 0.000 0.000 ↓ 0.0 0

CTE Scan on temp_story temp_story_3 (cost=0.00..56.90 rows=2,845 width=118) (never executed)

75. 1.020 1.020 ↓ 0.0 0 102

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

  • Index Cond: (obj_id = (cmn_search_tbl.object_id)::text)
  • Heap Fetches: 0
76.          

SubPlan (for Nested Loop Left Join)

77. 0.102 1.122 ↑ 1.0 1 102

Aggregate (cost=4.67..4.68 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=102)

78. 1.020 1.020 ↓ 0.0 0 102

Index Scan using vis_story_user_map_tbl_pkey on vis_story_user_map_tbl vis_story_user_map_tbl_1 (cost=0.29..4.67 rows=1 width=2) (actual time=0.010..0.010 rows=0 loops=102)

  • Index Cond: ((story_id)::text = (cmn_search_tbl.object_id)::text)
  • Filter: (lower((user_id)::text) = 'zicc232'::text)
  • Rows Removed by Filter: 1
79. 0.408 4,889.166 ↑ 1.0 1 102

Aggregate (cost=39,943,738.35..39,943,738.36 rows=1 width=32) (actual time=47.933..47.933 rows=1 loops=102)

80. 4,888.758 4,888.758 ↑ 72,711.0 1 102

Seq Scan on vis_story_grp_map_tbl vis_story_grp_map_tbl_1 (cost=0.00..39,943,556.57 rows=72,711 width=2) (actual time=27.780..47.929 rows=1 loops=102)

  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 145421
81.          

SubPlan (for Seq Scan)

82. 0.000 0.000 ↓ 0.0 0 14,833,044

Result (cost=8.77..538.88 rows=652 width=8) (actual time=0.000..0.000 rows=0 loops=14,833,044)

  • One-Time Filter: ((vis_story_grp_map_tbl_1.story_id)::text = (cmn_search_tbl.object_id)::text)
83. 4.884 8.954 ↑ 40.8 16 814

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.006..0.011 rows=16 loops=814)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=9702
84. 4.070 4.070 ↑ 40.8 16 814

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..8.61 rows=652 width=0) (actual time=0.005..0.005 rows=16 loops=814)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
Planning time : 3.682 ms
Execution time : 5,360.848 ms