explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a8wY

Settings
# exclusive inclusive rows x rows loops node
1. 26.530 5,123.101 ↓ 8,998.0 8,998 1

WindowAgg (cost=11,640.99..11,691.14 rows=1 width=264) (actual time=322.443..5,123.101 rows=8,998 loops=1)

2.          

CTE temp_story

3. 3.438 45.981 ↓ 27.6 5,530 1

HashAggregate (cost=3,699.04..3,701.04 rows=200 width=150) (actual time=44.616..45.981 rows=5,530 loops=1)

  • Group Key: story_tbl_1.story_id
4. 3.433 42.543 ↑ 1.1 5,530 1

HashAggregate (cost=3,549.99..3,609.61 rows=5,962 width=150) (actual time=41.373..42.543 rows=5,530 loops=1)

  • Group Key: story_tbl_1.story_id, ('Y'::bpchar)
5. 0.451 39.110 ↑ 1.1 5,530 1

Append (cost=0.00..3,520.18 rows=5,962 width=150) (actual time=0.282..39.110 rows=5,530 loops=1)

6. 6.265 6.265 ↑ 9.6 7 1

Seq Scan on story_tbl story_tbl_1 (cost=0.00..484.92 rows=67 width=38) (actual time=0.282..6.265 rows=7 loops=1)

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

Nested Loop (cost=484.45..633.44 rows=79 width=38) (actual time=7.102..7.102 rows=1 loops=1)

8. 0.004 7.080 ↑ 79.0 1 1

GroupAggregate (cost=484.16..485.55 rows=79 width=37) (actual time=7.080..7.080 rows=1 loops=1)

  • Group Key: vis_story_user_map_tbl.story_id
9. 0.010 7.076 ↑ 80.0 1 1

Sort (cost=484.16..484.36 rows=80 width=7) (actual time=7.076..7.076 rows=1 loops=1)

  • Sort Key: vis_story_user_map_tbl.story_id
  • Sort Method: quicksort Memory: 25kB
10. 7.066 7.066 ↑ 80.0 1 1

Seq Scan on vis_story_user_map_tbl (cost=0.00..481.64 rows=80 width=7) (actual time=1.295..7.066 rows=1 loops=1)

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

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

  • Index Cond: (story_id = (vis_story_user_map_tbl.story_id)::text)
  • Heap Fetches: 1
12. 3.334 25.292 ↑ 1.1 5,522 1

Hash Join (cost=1,864.81..2,312.38 rows=5,816 width=38) (actual time=19.654..25.292 rows=5,522 loops=1)

  • Hash Cond: ((sty_1.story_id)::text = (gsty.story_id)::text)
13. 2.453 2.453 ↑ 1.0 15,728 1

Seq Scan on story_tbl sty_1 (cost=0.00..406.28 rows=15,728 width=6) (actual time=0.005..2.453 rows=15,728 loops=1)

14. 1.016 19.505 ↑ 1.1 5,522 1

Hash (cost=1,792.11..1,792.11 rows=5,816 width=37) (actual time=19.505..19.505 rows=5,522 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
15. 0.506 18.489 ↑ 1.1 5,522 1

Subquery Scan on gsty (cost=1,675.79..1,792.11 rows=5,816 width=37) (actual time=16.823..18.489 rows=5,522 loops=1)

16. 3.032 17.983 ↑ 1.1 5,522 1

HashAggregate (cost=1,675.79..1,733.95 rows=5,816 width=37) (actual time=16.822..17.983 rows=5,522 loops=1)

  • Group Key: vis_story_grp_map_tbl.story_id
17. 5.068 14.951 ↑ 1.1 7,798 1

Hash Join (cost=172.96..1,633.36 rows=8,486 width=7) (actual time=0.684..14.951 rows=7,798 loops=1)

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
18. 9.832 9.832 ↑ 1.0 48,839 1

Seq Scan on vis_story_grp_map_tbl (cost=0.00..1,206.49 rows=48,839 width=12) (actual time=0.008..9.832 rows=48,839 loops=1)

  • Filter: ((org_name IS NULL) AND ((story_type_id)::text = '2'::text))
19. 0.004 0.051 ↑ 18.6 10 1

Hash (cost=170.63..170.63 rows=186 width=8) (actual time=0.051..0.051 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.008 0.047 ↑ 18.6 10 1

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

  • Group Key: ((adm_grp_usr_map.group_id)::character varying)::text
21. 0.021 0.039 ↑ 20.0 10 1

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

  • Recheck Cond: (lower((user_id)::text) = 'zicc232'::text)
  • Heap Blocks: exact=6
22. 0.018 0.018 ↑ 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.018..0.018 rows=10 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
23. 4.273 5,096.571 ↓ 8,998.0 8,998 1

Nested Loop Left Join (cost=7,939.95..7,990.08 rows=1 width=208) (actual time=321.364..5,096.571 rows=8,998 loops=1)

24. 1,569.287 5,056.306 ↓ 8,998.0 8,998 1

Nested Loop Left Join (cost=7,939.54..7,987.44 rows=1 width=202) (actual time=321.347..5,056.306 rows=8,998 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_report_map.story_id)::text)
  • Rows Removed by Join Filter: 24700443
25. 2.579 346.717 ↓ 8,998.0 8,998 1

Merge Left Join (cost=6,801.21..6,820.24 rows=1 width=184) (actual time=253.995..346.717 rows=8,998 loops=1)

  • Merge Cond: ((story_tbl.story_id)::text = (story_exec_info_tbl.story_id)::text)
26. 2.208 319.583 ↓ 8,998.0 8,998 1

Nested Loop Left Join (cost=3,136.71..3,149.40 rows=1 width=176) (actual time=229.485..319.583 rows=8,998 loops=1)

27. 7.249 290.381 ↓ 8,998.0 8,998 1

Nested Loop (cost=3,136.43..3,146.89 rows=1 width=171) (actual time=229.473..290.381 rows=8,998 loops=1)

  • Join Filter: ((cmn_search_tbl.object_id)::text = (story_tbl.story_id)::text)
28. 6.635 241.444 ↓ 1,488.9 10,422 1

Merge Join (cost=3,136.15..3,137.37 rows=7 width=214) (actual time=229.449..241.444 rows=10,422 loops=1)

  • Merge Cond: ((cmn_search_tbl.object_id)::text = (tmp.story_id)::text)
29. 2.316 222.366 ↓ 1,488.9 10,422 1

Unique (cost=3,124.50..3,124.57 rows=7 width=64) (actual time=218.341..222.366 rows=10,422 loops=1)

30. 22.847 220.050 ↓ 1,488.9 10,422 1

Sort (cost=3,124.50..3,124.52 rows=7 width=64) (actual time=218.340..220.050 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
31. 2.425 197.203 ↓ 1,488.9 10,422 1

Nested Loop (cost=36.68..3,124.40 rows=7 width=64) (actual time=104.746..197.203 rows=10,422 loops=1)

32. 0.567 106.191 ↓ 1,302.8 5,211 1

Subquery Scan on ANY_subquery (cost=36.13..3,072.99 rows=4 width=118) (actual time=104.697..106.191 rows=5,211 loops=1)

33. 1.974 105.624 ↓ 1,302.8 5,211 1

HashSetOp Except (cost=36.13..3,072.95 rows=4 width=122) (actual time=104.696..105.624 rows=5,211 loops=1)

34. 0.357 103.650 ↓ 744.4 5,211 1

Append (cost=36.13..3,072.93 rows=7 width=122) (actual time=76.260..103.650 rows=5,211 loops=1)

35. 0.498 77.304 ↓ 1,302.8 5,211 1

Result (cost=36.13..1,751.07 rows=4 width=122) (actual time=76.259..77.304 rows=5,211 loops=1)

36. 1.763 76.806 ↓ 1,302.8 5,211 1

HashSetOp Except (cost=36.13..1,751.03 rows=4 width=122) (actual time=76.258..76.806 rows=5,211 loops=1)

37. 0.390 75.043 ↓ 868.5 5,211 1

Append (cost=36.13..1,751.02 rows=6 width=122) (actual time=54.345..75.043 rows=5,211 loops=1)

38. 0.523 74.564 ↓ 1,302.8 5,211 1

Subquery Scan on *SELECT* 1 (cost=36.13..1,733.56 rows=4 width=41) (actual time=54.344..74.564 rows=5,211 loops=1)

39. 2.517 74.041 ↓ 1,302.8 5,211 1

Hash Semi Join (cost=36.13..1,733.52 rows=4 width=37) (actual time=54.343..74.041 rows=5,211 loops=1)

  • Hash Cond: ((cmn_search_tbl_1.object_id)::text = (temp_story.story_id)::text)
40. 20.608 22.564 ↓ 9.1 15,133 1

Bitmap Heap Scan on cmn_search_tbl cmn_search_tbl_1 (cost=29.63..1,722.50 rows=1,669 width=37) (actual time=2.734..22.564 rows=15,133 loops=1)

  • Recheck Cond: ((lower((keyword)::text) = 'story'::text) AND (lower((type)::text) = 'object type'::text))
  • Heap Blocks: exact=6336
41. 1.956 1.956 ↓ 9.1 15,133 1

Bitmap Index Scan on cmn_search_tbl_key_type_i (cost=0.00..29.22 rows=1,669 width=0) (actual time=1.956..1.956 rows=15,133 loops=1)

  • Index Cond: ((lower((keyword)::text) = 'story'::text) AND (lower((type)::text) = 'object type'::text))
42. 0.990 48.960 ↓ 27.6 5,530 1

Hash (cost=4.00..4.00 rows=200 width=118) (actual time=48.960..48.960 rows=5,530 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 282kB
43. 47.970 47.970 ↓ 27.6 5,530 1

CTE Scan on temp_story (cost=0.00..4.00 rows=200 width=118) (actual time=44.620..47.970 rows=5,530 loops=1)

44. 0.000 0.089 ↓ 0.0 0 1

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

45. 0.011 0.089 ↓ 0.0 0 1

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

  • Hash Cond: ((vm.desk_id)::text = (vd.desk_id)::text)
46. 0.015 0.015 ↑ 505.0 1 1

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

47. 0.000 0.063 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
48. 0.063 0.063 ↓ 0.0 0 1

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

  • Filter: ((target_app)::text = 'AE'::text)
  • Rows Removed by Filter: 240
49. 0.000 25.989 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=28.49..1,321.82 rows=3 width=41) (actual time=25.989..25.989 rows=0 loops=1)

50. 2.890 25.989 ↓ 0.0 0 1

Hash Semi Join (cost=28.49..1,321.79 rows=3 width=37) (actual time=25.988..25.989 rows=0 loops=1)

  • Hash Cond: ((cmn_search_tbl_2.object_id)::text = (temp_story_1.story_id)::text)
51. 19.458 21.754 ↓ 13.0 16,204 1

Bitmap Heap Scan on cmn_search_tbl cmn_search_tbl_2 (cost=21.99..1,311.92 rows=1,245 width=37) (actual time=3.098..21.754 rows=16,204 loops=1)

  • Recheck Cond: ((lower((keyword)::text) = 'false'::text) AND (lower((type)::text) = 'isdisplayinlibrary'::text))
  • Heap Blocks: exact=6557
52. 2.296 2.296 ↓ 13.0 16,204 1

Bitmap Index Scan on cmn_search_tbl_key_type_i (cost=0.00..21.68 rows=1,245 width=0) (actual time=2.296..2.296 rows=16,204 loops=1)

  • Index Cond: ((lower((keyword)::text) = 'false'::text) AND (lower((type)::text) = 'isdisplayinlibrary'::text))
53. 0.745 1.345 ↓ 27.6 5,530 1

Hash (cost=4.00..4.00 rows=200 width=118) (actual time=1.345..1.345 rows=5,530 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 282kB
54. 0.600 0.600 ↓ 27.6 5,530 1

CTE Scan on temp_story temp_story_1 (cost=0.00..4.00 rows=200 width=118) (actual time=0.001..0.600 rows=5,530 loops=1)

55. 88.587 88.587 ↑ 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.016..0.017 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
56. 11.905 12.443 ↓ 53.7 10,740 1

Sort (cost=11.64..12.14 rows=200 width=150) (actual time=11.101..12.443 rows=10,740 loops=1)

  • Sort Key: tmp.story_id
  • Sort Method: quicksort Memory: 452kB
57. 0.538 0.538 ↓ 27.6 5,530 1

CTE Scan on temp_story tmp (cost=0.00..4.00 rows=200 width=150) (actual time=0.002..0.538 rows=5,530 loops=1)

58. 41.688 41.688 ↑ 1.0 1 10,422

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

  • Index Cond: ((story_id)::text = (tmp.story_id)::text)
  • Filter: ((type IS NULL) AND (org_name IS NULL) AND (guided_story_id IS NULL))
  • Rows Removed by Filter: 0
59. 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))
60. 0.040 24.555 ↑ 11.7 18 1

GroupAggregate (cost=3,664.50..3,668.19 rows=211 width=13) (actual time=24.504..24.555 rows=18 loops=1)

  • Group Key: story_exec_info_tbl.story_id
61. 0.067 24.515 ↑ 2.7 79 1

Sort (cost=3,664.50..3,665.02 rows=211 width=5) (actual time=24.499..24.515 rows=79 loops=1)

  • Sort Key: story_exec_info_tbl.story_id
  • Sort Method: quicksort Memory: 28kB
62. 0.489 24.448 ↑ 2.7 79 1

Hash Join (cost=1,009.00..3,656.35 rows=211 width=5) (actual time=22.920..24.448 rows=79 loops=1)

  • Hash Cond: ((story_exec_info_tbl.story_id)::text = (temp_story_2.story_id)::text)
63. 14.876 20.921 ↓ 1.0 7,323 1

Gather (cost=1,000.00..3,626.15 rows=7,183 width=5) (actual time=19.836..20.921 rows=7,323 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
64. 6.045 6.045 ↑ 1.1 915 8

Parallel Seq Scan on story_exec_info_tbl (cost=0.00..1,907.85 rows=1,026 width=5) (actual time=5.433..6.045 rows=915 loops=8)

  • Filter: ((executed_time <= CURRENT_TIMESTAMP) AND (executed_time >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 12495
65. 0.718 3.038 ↓ 27.6 5,530 1

Hash (cost=6.50..6.50 rows=200 width=118) (actual time=3.038..3.038 rows=5,530 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 282kB
66. 1.801 2.320 ↓ 27.6 5,530 1

HashAggregate (cost=4.50..6.50 rows=200 width=118) (actual time=1.755..2.320 rows=5,530 loops=1)

  • Group Key: (temp_story_2.story_id)::text
67. 0.519 0.519 ↓ 27.6 5,530 1

CTE Scan on temp_story temp_story_2 (cost=0.00..4.00 rows=200 width=118) (actual time=0.001..0.519 rows=5,530 loops=1)

68. 3,078.407 3,140.302 ↓ 3.1 2,746 8,998

HashAggregate (cost=1,138.33..1,147.21 rows=888 width=30) (actual time=0.008..0.349 rows=2,746 loops=8,998)

  • Group Key: story_report_map.story_id
69. 9.235 61.895 ↓ 25.4 22,549 1

Nested Loop (cost=4.92..1,129.45 rows=888 width=267) (actual time=1.838..61.895 rows=22,549 loops=1)

70. 2.337 2.890 ↓ 27.6 5,530 1

HashAggregate (cost=4.50..6.50 rows=200 width=118) (actual time=1.808..2.890 rows=5,530 loops=1)

  • Group Key: (temp_story_3.story_id)::text
71. 0.553 0.553 ↓ 27.6 5,530 1

CTE Scan on temp_story temp_story_3 (cost=0.00..4.00 rows=200 width=118) (actual time=0.002..0.553 rows=5,530 loops=1)

72. 49.770 49.770 ↑ 1.0 4 5,530

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

  • Index Cond: ((story_id)::text = (temp_story_3.story_id)::text)
73. 35.992 35.992 ↓ 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.004..0.004 rows=0 loops=8,998)

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