explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a3do

Settings
# exclusive inclusive rows x rows loops node
1. 29.441 11,336.943 ↓ 8,998.0 8,998 1

WindowAgg (cost=7,357.51..7,375.98 rows=1 width=264) (actual time=395.802..11,336.943 rows=8,998 loops=1)

2.          

CTE temp_story

3. 1.922 64.967 ↓ 125.7 5,530 1

GroupAggregate (cost=2,290.64..2,291.41 rows=44 width=150) (actual time=62.502..64.967 rows=5,530 loops=1)

  • Group Key: story_tbl_1.story_id
4. 10.394 63.045 ↓ 125.7 5,530 1

Sort (cost=2,290.64..2,290.75 rows=44 width=150) (actual time=62.498..63.045 rows=5,530 loops=1)

  • Sort Key: story_tbl_1.story_id
  • Sort Method: quicksort Memory: 452kB
5. 2.210 52.651 ↓ 125.7 5,530 1

HashAggregate (cost=2,288.56..2,289.00 rows=44 width=150) (actual time=51.942..52.651 rows=5,530 loops=1)

  • Group Key: story_tbl_1.story_id, ('Y'::bpchar)
6. 0.359 50.441 ↓ 125.7 5,530 1

Append (cost=0.00..2,288.34 rows=44 width=150) (actual time=0.271..50.441 rows=5,530 loops=1)

7. 5.837 5.837 ↓ 7.0 7 1

Seq Scan on story_tbl story_tbl_1 (cost=0.00..524.24 rows=1 width=38) (actual time=0.270..5.837 rows=7 loops=1)

  • Filter: ((type IS NULL) AND (guided_story_id IS NULL) AND (lower((org_name)::text) IS NULL) AND (lower((created_by)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 15721
8. 0.003 6.064 ↑ 1.0 1 1

Nested Loop (cost=521.74..523.98 rows=1 width=38) (actual time=6.064..6.064 rows=1 loops=1)

9. 0.004 6.045 ↑ 1.0 1 1

GroupAggregate (cost=521.45..521.47 rows=1 width=37) (actual time=6.045..6.045 rows=1 loops=1)

  • Group Key: vis_story_user_map_tbl.story_id
10. 0.008 6.041 ↑ 1.0 1 1

Sort (cost=521.45..521.46 rows=1 width=7) (actual time=6.041..6.041 rows=1 loops=1)

  • Sort Key: vis_story_user_map_tbl.story_id
  • Sort Method: quicksort Memory: 25kB
11. 6.033 6.033 ↑ 1.0 1 1

Seq Scan on vis_story_user_map_tbl (cost=0.00..521.44 rows=1 width=7) (actual time=1.105..6.033 rows=1 loops=1)

  • Filter: ((lower((org_name)::text) IS NULL) AND ((story_type_id)::text = '2'::text) AND (lower((user_id)::text) = 'zicc232'::text))
  • Rows Removed by Filter: 15921
12. 0.016 0.016 ↑ 1.0 1 1

Index Only Scan using story_tbl_pkey on story_tbl sty (cost=0.29..2.50 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (story_id = (vis_story_user_map_tbl.story_id)::text)
  • Heap Fetches: 1
13. 3.202 38.181 ↓ 131.5 5,522 1

Nested Loop (cost=1,149.98..1,239.46 rows=42 width=38) (actual time=16.145..38.181 rows=5,522 loops=1)

14. 1.888 18.413 ↓ 131.5 5,522 1

GroupAggregate (cost=1,149.70..1,150.43 rows=42 width=37) (actual time=16.131..18.413 rows=5,522 loops=1)

  • Group Key: vis_story_grp_map_tbl.story_id
15. 13.687 16.525 ↓ 185.7 7,798 1

Sort (cost=1,149.70..1,149.80 rows=42 width=7) (actual time=16.127..16.525 rows=7,798 loops=1)

  • Sort Key: vis_story_grp_map_tbl.story_id
  • Sort Method: quicksort Memory: 558kB
16. 0.665 2.838 ↓ 185.7 7,798 1

Nested Loop (cost=169.07..1,148.56 rows=42 width=7) (actual time=0.069..2.838 rows=7,798 loops=1)

17. 0.008 0.043 ↑ 18.6 10 1

HashAggregate (cost=168.77..170.63 rows=186 width=8) (actual time=0.040..0.043 rows=10 loops=1)

  • Group Key: ((adm_grp_usr_map.group_id)::character varying)::text
18. 0.019 0.035 ↑ 20.0 10 1

Bitmap Heap Scan on adm_grp_usr_map (cost=2.94..168.27 rows=200 width=8) (actual time=0.022..0.035 rows=10 loops=1)

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=6
19. 0.016 0.016 ↑ 20.0 10 1

Bitmap Index Scan on grp_usr_map_usrid_grpid_i (cost=0.00..2.89 rows=200 width=0) (actual time=0.016..0.016 rows=10 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
20. 2.130 2.130 ↓ 780.0 780 10

Index Scan using idx_vis_story_grp_map_tbl on vis_story_grp_map_tbl (cost=0.29..5.25 rows=1 width=12) (actual time=0.007..0.213 rows=780 loops=10)

  • Index Cond: ((group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
  • Filter: ((lower((org_name)::text) IS NULL) AND ((story_type_id)::text = '2'::text))
21. 16.566 16.566 ↑ 1.0 1 5,522

Index Only Scan using story_tbl_pkey on story_tbl sty_1 (cost=0.29..2.11 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=5,522)

  • Index Cond: (story_id = (vis_story_grp_map_tbl.story_id)::text)
  • Heap Fetches: 5522
22. 0.341 11,307.502 ↓ 8,998.0 8,998 1

Nested Loop Left Join (cost=5,066.09..5,084.54 rows=1 width=208) (actual time=393.360..11,307.502 rows=8,998 loops=1)

23. 1,504.821 11,262.171 ↓ 8,998.0 8,998 1

Nested Loop Left Join (cost=5,065.68..5,081.90 rows=1 width=202) (actual time=393.338..11,262.171 rows=8,998 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_report_map.story_id)::text)
  • Rows Removed by Join Filter: 24700173
24. 3.332 6,725.024 ↓ 8,998.0 8,998 1

Merge Left Join (cost=4,801.43..4,811.31 rows=1 width=184) (actual time=330.210..6,725.024 rows=8,998 loops=1)

  • Merge Cond: ((story_tbl.story_id)::text = (story_exec_info_tbl.story_id)::text)
25. 6.701 6,695.836 ↓ 8,998.0 8,998 1

Nested Loop Left Join (cost=1,153.21..1,161.71 rows=1 width=176) (actual time=304.407..6,695.836 rows=8,998 loops=1)

26. 3,156.929 6,662.141 ↓ 8,998.0 8,998 1

Nested Loop (cost=1,152.93..1,159.20 rows=1 width=171) (actual time=304.392..6,662.141 rows=8,998 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (tmp.story_id)::text)
  • Rows Removed by Join Filter: 49749942
27. 3.475 364.910 ↓ 8,998.0 8,998 1

Nested Loop (cost=1,152.93..1,157.77 rows=1 width=139) (actual time=304.388..364.910 rows=8,998 loops=1)

28. 2.429 309.325 ↓ 5,211.0 10,422 1

Unique (cost=1,152.65..1,152.67 rows=2 width=64) (actual time=304.367..309.325 rows=10,422 loops=1)

29. 22.250 306.896 ↓ 5,211.0 10,422 1

Sort (cost=1,152.65..1,152.65 rows=2 width=64) (actual time=304.366..306.896 rows=10,422 loops=1)

  • Sort Key: cmn_search_tbl.object_id, cmn_search_tbl.type, cmn_search_tbl.keyword
  • Sort Method: quicksort Memory: 1198kB
30. 1.157 284.646 ↓ 5,211.0 10,422 1

Nested Loop (cost=2.09..1,152.64 rows=2 width=64) (actual time=214.468..284.646 rows=10,422 loops=1)

31. 0.459 215.746 ↓ 5,211.0 5,211 1

Subquery Scan on ANY_subquery (cost=1.54..1,139.78 rows=1 width=118) (actual time=214.442..215.746 rows=5,211 loops=1)

32. 1.864 215.287 ↓ 5,211.0 5,211 1

HashSetOp Except (cost=1.54..1,139.77 rows=1 width=122) (actual time=214.441..215.287 rows=5,211 loops=1)

33. 0.303 213.423 ↓ 2,605.5 5,211 1

Append (cost=1.54..1,139.77 rows=2 width=122) (actual time=140.624..213.423 rows=5,211 loops=1)

34. 0.404 141.292 ↓ 5,211.0 5,211 1

Result (cost=1.54..578.61 rows=1 width=122) (actual time=140.624..141.292 rows=5,211 loops=1)

35. 1.495 140.888 ↓ 5,211.0 5,211 1

HashSetOp Except (cost=1.54..578.60 rows=1 width=122) (actual time=140.622..140.888 rows=5,211 loops=1)

36. 0.340 139.393 ↓ 1,737.0 5,211 1

Append (cost=1.54..578.59 rows=3 width=122) (actual time=68.365..139.393 rows=5,211 loops=1)

37. 0.438 138.992 ↓ 5,211.0 5,211 1

Subquery Scan on *SELECT* 1 (cost=1.54..561.15 rows=1 width=41) (actual time=68.365..138.992 rows=5,211 loops=1)

38. 2.965 138.554 ↓ 5,211.0 5,211 1

Nested Loop (cost=1.54..561.14 rows=1 width=37) (actual time=68.363..138.554 rows=5,211 loops=1)

39. 2.580 69.229 ↓ 125.7 5,530 1

HashAggregate (cost=0.99..1.43 rows=44 width=118) (actual time=68.312..69.229 rows=5,530 loops=1)

  • Group Key: (temp_story.story_id)::text
40. 66.649 66.649 ↓ 125.7 5,530 1

CTE Scan on temp_story (cost=0.00..0.88 rows=44 width=118) (actual time=62.505..66.649 rows=5,530 loops=1)

41. 66.360 66.360 ↑ 1.0 1 5,530

Index Scan using cmn_search_idx1 on cmn_search_tbl cmn_search_tbl_1 (cost=0.55..12.71 rows=1 width=37) (actual time=0.011..0.012 rows=1 loops=5,530)

  • 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: 9
42. 0.001 0.061 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=8.01..17.43 rows=2 width=9) (actual time=0.061..0.061 rows=0 loops=1)

43. 0.008 0.060 ↓ 0.0 0 1

Hash Join (cost=8.01..17.41 rows=2 width=5) (actual time=0.060..0.060 rows=0 loops=1)

  • Hash Cond: ((vm.desk_id)::text = (vd.desk_id)::text)
44. 0.011 0.011 ↑ 505.0 1 1

Seq Scan on vis_desk_story_map vm (cost=0.00..8.05 rows=505 width=10) (actual time=0.011..0.011 rows=1 loops=1)

45. 0.000 0.041 ↓ 0.0 0 1

Hash (cost=8.00..8.00 rows=1 width=5) (actual time=0.041..0.041 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.041 0.041 ↓ 0.0 0 1

Seq Scan on vis_desk vd (cost=0.00..8.00 rows=1 width=5) (actual time=0.041..0.041 rows=0 loops=1)

  • Filter: ((target_app)::text = 'AE'::text)
  • Rows Removed by Filter: 240
47. 0.001 71.828 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=1.54..561.15 rows=1 width=41) (actual time=71.828..71.828 rows=0 loops=1)

48. 2.550 71.827 ↓ 0.0 0 1

Nested Loop (cost=1.54..561.14 rows=1 width=37) (actual time=71.827..71.827 rows=0 loops=1)

49. 2.408 2.917 ↓ 125.7 5,530 1

HashAggregate (cost=0.99..1.43 rows=44 width=118) (actual time=1.709..2.917 rows=5,530 loops=1)

  • Group Key: (temp_story_1.story_id)::text
50. 0.509 0.509 ↓ 125.7 5,530 1

CTE Scan on temp_story temp_story_1 (cost=0.00..0.88 rows=44 width=118) (actual time=0.003..0.509 rows=5,530 loops=1)

51. 66.360 66.360 ↓ 0.0 0 5,530

Index Scan using cmn_search_idx1 on cmn_search_tbl cmn_search_tbl_2 (cost=0.55..12.71 rows=1 width=37) (actual time=0.012..0.012 rows=0 loops=5,530)

  • 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: 10
52. 67.743 67.743 ↑ 1.0 2 5,211

Index Scan using cmn_search_idx1 on cmn_search_tbl (cost=0.55..12.83 rows=2 width=64) (actual time=0.012..0.013 rows=2 loops=5,211)

  • 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: 8
53. 52.110 52.110 ↑ 1.0 1 10,422

Index Scan using story_tbl_pkey on story_tbl (cost=0.29..2.51 rows=1 width=75) (actual time=0.005..0.005 rows=1 loops=10,422)

  • 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) IS NULL))
  • Rows Removed by Filter: 0
54. 3,140.302 3,140.302 ↓ 125.7 5,530 8,998

CTE Scan on temp_story tmp (cost=0.00..0.88 rows=44 width=150) (actual time=0.000..0.349 rows=5,530 loops=8,998)

55. 26.994 26.994 ↓ 0.0 0 8,998

Index Scan using favourites_tbl_objid_usrid_i on favourites_tbl fav (cost=0.28..2.50 rows=1 width=44) (actual time=0.003..0.003 rows=0 loops=8,998)

  • Index Cond: (((cmn_search_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
56. 0.047 25.856 ↑ 2.6 18 1

GroupAggregate (cost=3,648.22..3,649.02 rows=46 width=13) (actual time=25.796..25.856 rows=18 loops=1)

  • Group Key: story_exec_info_tbl.story_id
57. 0.064 25.809 ↓ 1.7 79 1

Sort (cost=3,648.22..3,648.33 rows=46 width=5) (actual time=25.790..25.809 rows=79 loops=1)

  • Sort Key: story_exec_info_tbl.story_id
  • Sort Method: quicksort Memory: 28kB
58. 0.465 25.745 ↓ 1.7 79 1

Hash Semi Join (cost=1,001.43..3,646.94 rows=46 width=5) (actual time=24.252..25.745 rows=79 loops=1)

  • Hash Cond: ((story_exec_info_tbl.story_id)::text = (temp_story_2.story_id)::text)
59. 18.073 24.219 ↓ 1.0 7,327 1

Gather (cost=1,000.00..3,626.15 rows=7,183 width=5) (actual time=23.169..24.219 rows=7,327 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
60. 6.146 6.146 ↑ 1.1 916 8

Parallel Seq Scan on story_exec_info_tbl (cost=0.00..1,907.85 rows=1,026 width=5) (actual time=5.501..6.146 rows=916 loops=8)

  • Filter: ((executed_time <= CURRENT_TIMESTAMP) AND (executed_time >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 12495
61. 0.591 1.061 ↓ 125.7 5,530 1

Hash (cost=0.88..0.88 rows=44 width=118) (actual time=1.061..1.061 rows=5,530 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 282kB
62. 0.470 0.470 ↓ 125.7 5,530 1

CTE Scan on temp_story temp_story_2 (cost=0.00..0.88 rows=44 width=118) (actual time=0.001..0.470 rows=5,530 loops=1)

63. 2,974.776 3,032.326 ↓ 14.1 2,746 8,998

HashAggregate (cost=264.25..266.20 rows=195 width=30) (actual time=0.007..0.337 rows=2,746 loops=8,998)

  • Group Key: story_report_map.story_id
64. 10.379 57.550 ↓ 115.6 22,549 1

Nested Loop (cost=1.41..262.30 rows=195 width=267) (actual time=1.783..57.550 rows=22,549 loops=1)

65. 2.426 2.931 ↓ 125.7 5,530 1

HashAggregate (cost=0.99..1.43 rows=44 width=118) (actual time=1.762..2.931 rows=5,530 loops=1)

  • Group Key: (temp_story_3.story_id)::text
66. 0.505 0.505 ↓ 125.7 5,530 1

CTE Scan on temp_story temp_story_3 (cost=0.00..0.88 rows=44 width=118) (actual time=0.001..0.505 rows=5,530 loops=1)

67. 44.240 44.240 ↑ 1.0 4 5,530

Index Scan using story_report_map_index on story_report_map (cost=0.42..5.89 rows=4 width=267) (actual time=0.006..0.008 rows=4 loops=5,530)

  • Index Cond: ((story_id)::text = (temp_story_3.story_id)::text)
68. 44.990 44.990 ↓ 0.0 0 8,998

Index Only Scan using inx_objcollmap_objid_collid on object_collection_map objcolmap (cost=0.41..2.63 rows=1 width=46) (actual time=0.005..0.005 rows=0 loops=8,998)

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