explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jCQV

Settings
# exclusive inclusive rows x rows loops node
1. 0.340 215.556 ↓ 102.0 102 1

WindowAgg (cost=34,283.35..34,368.86 rows=1 width=283) (actual time=170.167..215.556 rows=102 loops=1)

2.          

CTE temp_story

3. 1.303 62.827 ↓ 15.5 3,101 1

HashAggregate (cost=14,380.35..14,382.35 rows=200 width=150) (actual time=62.376..62.827 rows=3,101 loops=1)

  • Group Key: story_tbl_1.story_id
4. 1.184 61.524 ↓ 1.1 3,101 1

HashAggregate (cost=14,309.23..14,337.68 rows=2,845 width=150) (actual time=61.203..61.524 rows=3,101 loops=1)

  • Group Key: story_tbl_1.story_id, ('Y'::bpchar)
5. 0.008 60.340 ↓ 1.1 3,101 1

Append (cost=1,000.00..14,295.00 rows=2,845 width=150) (actual time=3.050..60.340 rows=3,101 loops=1)

6. 15.797 20.810 ↑ 2.6 18 1

Gather (cost=1,000.00..4,681.19 rows=46 width=39) (actual time=3.049..20.810 rows=18 loops=1)

  • Workers Planned: 8
  • Workers Launched: 8
7. 5.013 5.013 ↑ 3.0 2 9

Parallel Seq Scan on story_tbl story_tbl_1 (cost=0.00..3,676.59 rows=6 width=39) (actual time=1.815..5.013 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
8. 0.001 6.064 ↓ 0.0 0 1

Nested Loop (cost=1,458.33..1,503.21 rows=17 width=39) (actual time=6.064..6.064 rows=0 loops=1)

9. 0.002 6.063 ↓ 0.0 0 1

GroupAggregate (cost=1,457.91..1,458.20 rows=17 width=39) (actual time=6.063..6.063 rows=0 loops=1)

  • Group Key: vis_story_user_map_tbl.story_id
10. 0.011 6.061 ↓ 0.0 0 1

Sort (cost=1,457.91..1,457.95 rows=17 width=9) (actual time=6.061..6.061 rows=0 loops=1)

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

Seq Scan on vis_story_user_map_tbl (cost=0.00..1,457.56 rows=17 width=9) (actual time=6.050..6.050 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
12. 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
13. 0.000 33.458 ↓ 1.1 3,083 1

Nested Loop (cost=3,954.28..8,067.93 rows=2,782 width=39) (actual time=17.257..33.458 rows=3,083 loops=1)

14. 1.203 18.004 ↓ 1.1 3,125 1

HashAggregate (cost=3,953.86..3,981.68 rows=2,782 width=41) (actual time=17.233..18.004 rows=3,125 loops=1)

  • Group Key: vis_story_grp_map_tbl.story_id
15. 13.752 16.801 ↓ 1.2 3,230 1

Gather (cost=1,532.09..3,939.95 rows=2,782 width=11) (actual time=4.900..16.801 rows=3,230 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
16. 0.000 3.049 ↓ 1.0 404 8

Parallel Hash Semi Join (cost=532.09..2,661.75 rows=397 width=11) (actual time=0.958..3.049 rows=404 loops=8)

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
17. 4.259 4.259 ↓ 1.4 1,112 5

Parallel Seq Scan on vis_story_grp_map_tbl (cost=0.00..2,108.62 rows=795 width=16) (actual time=1.091..4.259 rows=1,112 loops=5)

  • Filter: (((org_name)::text = 'GSK_LD'::text) AND ((story_type_id)::text = '2'::text))
  • Rows Removed by Filter: 27972
18. 0.065 0.141 ↑ 54.5 2 8

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

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

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=12
20. 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)
21. 15.625 15.625 ↑ 1.0 1 3,125

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

  • Index Cond: (story_id = (vis_story_grp_map_tbl.story_id)::text)
  • Heap Fetches: 3083
22. 0.017 215.216 ↓ 102.0 102 1

Nested Loop Left Join (cost=19,901.00..19,986.48 rows=1 width=227) (actual time=169.763..215.216 rows=102 loops=1)

23. 1.075 214.587 ↓ 102.0 102 1

Nested Loop Left Join (cost=19,900.58..19,983.84 rows=1 width=221) (actual time=169.745..214.587 rows=102 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_report_map.story_id)::text)
  • Rows Removed by Join Filter: 16566
24. 0.020 193.010 ↓ 102.0 102 1

Nested Loop Left Join (cost=18,253.93..18,299.52 rows=1 width=204) (actual time=151.532..193.010 rows=102 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_exec_info_tbl.story_id)::text)
25. 0.091 154.944 ↓ 102.0 102 1

Nested Loop Left Join (cost=8,467.53..8,513.08 rows=1 width=196) (actual time=113.514..154.944 rows=102 loops=1)

26. 19.271 154.343 ↓ 102.0 102 1

Nested Loop (cost=8,467.12..8,510.44 rows=1 width=189) (actual time=113.497..154.343 rows=102 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (tmp.story_id)::text)
  • Rows Removed by Join Filter: 316200
27. 0.094 114.060 ↓ 102.0 102 1

Nested Loop (cost=8,467.12..8,503.94 rows=1 width=157) (actual time=113.221..114.060 rows=102 loops=1)

28. 0.030 113.252 ↓ 7.3 102 1

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

29. 0.140 113.222 ↓ 7.3 102 1

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

  • Sort Key: cmn_search_tbl.object_id, cmn_search_tbl.type, cmn_search_tbl.keyword
  • Sort Method: quicksort Memory: 32kB
30. 0.067 113.082 ↓ 7.3 102 1

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

31. 0.010 111.937 ↓ 19.6 98 1

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

32. 0.044 111.927 ↓ 19.6 98 1

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

33. 0.007 111.883 ↓ 5.8 98 1

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

34. 0.009 90.647 ↓ 19.6 98 1

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

35. 0.150 90.638 ↓ 19.6 98 1

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

36. 0.094 90.488 ↑ 1.3 1,620 1

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

37. 0.015 87.885 ↓ 19.6 98 1

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

38. 1.413 87.870 ↓ 19.6 98 1

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

39. 1.174 64.750 ↓ 15.5 3,101 1

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

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

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

41. 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
42. 0.110 2.509 ↑ 1.4 1,522 1

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

43. 0.467 2.399 ↑ 1.4 1,522 1

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

  • Hash Cond: ((vm.desk_id)::text = (vd.desk_id)::text)
44. 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)

45. 0.190 1.607 ↑ 1.0 1,522 1

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

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

  • Filter: ((target_app)::text = 'AE'::text)
  • Rows Removed by Filter: 1266
47. 0.000 21.229 ↓ 0.0 0 1

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

48. 1.116 21.229 ↓ 0.0 0 1

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

49. 1.205 1.507 ↓ 15.5 3,101 1

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

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

51. 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
52. 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.011..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
53. 0.714 0.714 ↑ 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.007..0.007 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 ((org_name)::text = 'GSK_LD'::text))
54. 21.012 21.012 ↓ 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.206 rows=3,101 loops=102)

55. 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))
56. 0.000 38.046 ↓ 0.0 0 102

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

  • Group Key: story_exec_info_tbl.story_id
57. 0.041 38.046 ↓ 0.0 0 102

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

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

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

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

  • Workers Planned: 9
  • Workers Launched: 9
60. 22.281 22.281 ↓ 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.281..22.281 rows=0 loops=10)

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

62. 2.498 20.502 ↑ 7.1 163 102

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

  • Group Key: story_report_map.story_id
63. 0.969 18.004 ↑ 1.5 788 1

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

64. 1.242 1.530 ↓ 15.5 3,101 1

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

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

66. 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)
67. 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 : 3.157 ms
Execution time : 216.494 ms