explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pKIJ

Settings
# exclusive inclusive rows x rows loops node
1. 24.836 5,066.764 ↓ 8,999.0 8,999 1

WindowAgg (cost=7,390.41..7,399.41 rows=1 width=263) (actual time=421.734..5,066.764 rows=8,999 loops=1)

2.          

CTE temp_story

3. 1.537 74.017 ↓ 1,382.8 5,531 1

GroupAggregate (cost=2,486.38..2,486.45 rows=4 width=150) (actual time=72.194..74.017 rows=5,531 loops=1)

  • Group Key: story_tbl_1.story_id
4. 10.703 72.480 ↓ 1,382.8 5,531 1

Sort (cost=2,486.38..2,486.39 rows=4 width=150) (actual time=72.190..72.480 rows=5,531 loops=1)

  • Sort Key: story_tbl_1.story_id
  • Sort Method: quicksort Memory: 452kB
5. 2.351 61.777 ↓ 1,382.8 5,531 1

HashAggregate (cost=2,486.26..2,486.30 rows=4 width=150) (actual time=61.023..61.777 rows=5,531 loops=1)

  • Group Key: story_tbl_1.story_id, ('Y'::bpchar)
6. 0.358 59.426 ↓ 1,382.8 5,531 1

Append (cost=0.00..2,486.24 rows=4 width=150) (actual time=0.254..59.426 rows=5,531 loops=1)

7. 6.346 6.346 ↓ 7.0 7 1

Seq Scan on story_tbl story_tbl_1 (cost=0.00..606.18 rows=1 width=38) (actual time=0.254..6.346 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: 15952
8. 0.003 6.374 ↓ 2.0 2 1

Nested Loop (cost=529.75..532.00 rows=1 width=38) (actual time=6.364..6.374 rows=2 loops=1)

9. 0.004 6.347 ↓ 2.0 2 1

GroupAggregate (cost=529.47..529.49 rows=1 width=37) (actual time=6.346..6.347 rows=2 loops=1)

  • Group Key: vis_story_user_map_tbl.story_id
10. 0.008 6.343 ↓ 2.0 2 1

Sort (cost=529.47..529.48 rows=1 width=7) (actual time=6.342..6.343 rows=2 loops=1)

  • Sort Key: vis_story_user_map_tbl.story_id
  • Sort Method: quicksort Memory: 25kB
11. 6.335 6.335 ↓ 2.0 2 1

Seq Scan on vis_story_user_map_tbl (cost=0.00..529.46 rows=1 width=7) (actual time=1.068..6.335 rows=2 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: 16039
12. 0.024 0.024 ↑ 1.0 1 2

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

  • Index Cond: (story_id = (vis_story_user_map_tbl.story_id)::text)
  • Heap Fetches: 2
13. 0.690 46.348 ↓ 2,761.0 5,522 1

Nested Loop (cost=1,343.24..1,348.02 rows=2 width=38) (actual time=26.565..46.348 rows=5,522 loops=1)

14. 2.129 29.092 ↓ 2,761.0 5,522 1

GroupAggregate (cost=1,342.96..1,342.99 rows=2 width=37) (actual time=26.547..29.092 rows=5,522 loops=1)

  • Group Key: vis_story_grp_map_tbl.story_id
15. 14.433 26.963 ↓ 3,899.0 7,798 1

Sort (cost=1,342.96..1,342.96 rows=2 width=7) (actual time=26.543..26.963 rows=7,798 loops=1)

  • Sort Key: vis_story_grp_map_tbl.story_id
  • Sort Method: quicksort Memory: 558kB
16. 4.407 12.530 ↓ 3,899.0 7,798 1

Hash Semi Join (cost=12.23..1,342.95 rows=2 width=7) (actual time=0.562..12.530 rows=7,798 loops=1)

  • Hash Cond: ((vis_story_grp_map_tbl.group_id)::text = ((adm_grp_usr_map.group_id)::character varying)::text)
17. 8.090 8.090 ↓ 200.3 48,866 1

Seq Scan on vis_story_grp_map_tbl (cost=0.00..1,329.99 rows=244 width=12) (actual time=0.004..8.090 rows=48,866 loops=1)

  • Filter: ((lower((org_name)::text) IS NULL) AND ((story_type_id)::text = '2'::text))
18. 0.003 0.033 ↑ 1.0 10 1

Hash (cost=12.11..12.11 rows=10 width=8) (actual time=0.033..0.033 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.030 0.030 ↑ 1.0 10 1

Index Scan using grp_usr_map_usrid_grpid_i on adm_grp_usr_map (cost=0.41..12.11 rows=10 width=8) (actual time=0.018..0.030 rows=10 loops=1)

  • Index Cond: (lower((user_id)::text) = 'zicc232'::text)
20. 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.50 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: 2316
21. 6.273 5,041.928 ↓ 8,999.0 8,999 1

Nested Loop Left Join (cost=4,903.96..4,912.93 rows=1 width=207) (actual time=420.611..5,041.928 rows=8,999 loops=1)

22. 1,499.626 5,008.658 ↓ 8,999.0 8,999 1

Nested Loop Left Join (cost=4,903.55..4,911.39 rows=1 width=201) (actual time=420.596..5,008.658 rows=8,999 loops=1)

  • Join Filter: ((story_tbl.story_id)::text = (story_report_map.story_id)::text)
  • Rows Removed by Join Filter: 24706516
23. 2.584 440.373 ↓ 8,999.0 8,999 1

Merge Left Join (cost=4,879.16..4,886.42 rows=1 width=183) (actual time=361.816..440.373 rows=8,999 loops=1)

  • Merge Cond: ((story_tbl.story_id)::text = (story_exec_info_tbl.story_id)::text)
24. 6.107 390.629 ↓ 8,999.0 8,999 1

Nested Loop Left Join (cost=715.17..722.23 rows=1 width=175) (actual time=314.712..390.629 rows=8,999 loops=1)

25. 9.246 366.524 ↓ 8,999.0 8,999 1

Nested Loop (cost=714.89..719.72 rows=1 width=170) (actual time=314.699..366.524 rows=8,999 loops=1)

  • Join Filter: ((cmn_search_tbl.object_id)::text = (story_tbl.story_id)::text)
26. 6.725 326.009 ↓ 5,211.5 10,423 1

Merge Join (cost=714.60..714.69 rows=2 width=213) (actual time=314.676..326.009 rows=10,423 loops=1)

  • Merge Cond: ((cmn_search_tbl.object_id)::text = (tmp.story_id)::text)
27. 2.051 315.727 ↓ 5,211.5 10,423 1

Unique (cost=714.48..714.50 rows=2 width=63) (actual time=312.076..315.727 rows=10,423 loops=1)

28. 22.679 313.676 ↓ 5,211.5 10,423 1

Sort (cost=714.48..714.49 rows=2 width=63) (actual time=312.075..313.676 rows=10,423 loops=1)

  • Sort Key: cmn_search_tbl.object_id, cmn_search_tbl.type, cmn_search_tbl.keyword
  • Sort Method: quicksort Memory: 1198kB
29. 1.041 290.997 ↓ 5,211.5 10,423 1

Nested Loop (cost=1.19..714.47 rows=2 width=63) (actual time=226.216..290.997 rows=10,423 loops=1)

30. 0.495 227.412 ↓ 5,212.0 5,212 1

Subquery Scan on ANY_subquery (cost=0.64..701.63 rows=1 width=118) (actual time=226.196..227.412 rows=5,212 loops=1)

31. 1.773 226.917 ↓ 5,212.0 5,212 1

HashSetOp Except (cost=0.64..701.62 rows=1 width=122) (actual time=226.195..226.917 rows=5,212 loops=1)

32. 0.298 225.144 ↓ 2,606.0 5,212 1

Append (cost=0.64..701.61 rows=2 width=122) (actual time=155.005..225.144 rows=5,212 loops=1)

33. 0.436 155.708 ↓ 5,212.0 5,212 1

Result (cost=0.64..649.94 rows=1 width=122) (actual time=155.005..155.708 rows=5,212 loops=1)

34. 1.625 155.272 ↓ 5,212.0 5,212 1

HashSetOp Except (cost=0.64..649.93 rows=1 width=122) (actual time=155.004..155.272 rows=5,212 loops=1)

35. 0.459 153.647 ↓ 2,606.0 5,212 1

Append (cost=0.64..649.92 rows=2 width=122) (actual time=76.484..153.647 rows=5,212 loops=1)

36. 0.522 152.857 ↓ 5,212.0 5,212 1

Subquery Scan on *SELECT* 1 (cost=0.64..51.68 rows=1 width=41) (actual time=76.484..152.857 rows=5,212 loops=1)

37. 2.731 152.335 ↓ 5,212.0 5,212 1

Nested Loop (cost=0.64..51.67 rows=1 width=37) (actual time=76.483..152.335 rows=5,212 loops=1)

38. 2.466 77.701 ↓ 1,382.8 5,531 1

HashAggregate (cost=0.09..0.13 rows=4 width=118) (actual time=76.446..77.701 rows=5,531 loops=1)

  • Group Key: (temp_story.story_id)::text
39. 75.235 75.235 ↓ 1,382.8 5,531 1

CTE Scan on temp_story (cost=0.00..0.08 rows=4 width=118) (actual time=72.196..75.235 rows=5,531 loops=1)

40. 71.903 71.903 ↑ 1.0 1 5,531

Index Scan using cmn_search_idx1 on cmn_search_tbl cmn_search_tbl_1 (cost=0.55..12.87 rows=1 width=37) (actual time=0.012..0.013 rows=1 loops=5,531)

  • 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
41. 0.001 0.331 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.28..598.25 rows=1 width=9) (actual time=0.331..0.331 rows=0 loops=1)

42. 0.000 0.330 ↓ 0.0 0 1

Nested Loop (cost=0.28..598.24 rows=1 width=5) (actual time=0.330..0.330 rows=0 loops=1)

  • Join Filter: ((vm.desk_id)::text = (vd.desk_id)::text)
43. 0.330 0.330 ↓ 0.0 0 1

Index Scan using vis_desk_pkey on vis_desk vd (cost=0.28..582.87 rows=1 width=5) (actual time=0.330..0.330 rows=0 loops=1)

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

Seq Scan on vis_desk_story_map vm (cost=0.00..9.05 rows=505 width=10) (never executed)

45. 0.001 69.138 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.64..51.68 rows=1 width=41) (actual time=69.138..69.138 rows=0 loops=1)

46. 0.000 69.137 ↓ 0.0 0 1

Nested Loop (cost=0.64..51.67 rows=1 width=37) (actual time=69.137..69.137 rows=0 loops=1)

47. 2.253 2.787 ↓ 1,382.8 5,531 1

HashAggregate (cost=0.09..0.13 rows=4 width=118) (actual time=1.832..2.787 rows=5,531 loops=1)

  • Group Key: (temp_story_1.story_id)::text
48. 0.534 0.534 ↓ 1,382.8 5,531 1

CTE Scan on temp_story temp_story_1 (cost=0.00..0.08 rows=4 width=118) (actual time=0.001..0.534 rows=5,531 loops=1)

49. 66.372 66.372 ↓ 0.0 0 5,531

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

  • 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
50. 62.544 62.544 ↑ 1.0 2 5,212

Index Scan using cmn_search_idx1 on cmn_search_tbl (cost=0.55..12.82 rows=2 width=63) (actual time=0.011..0.012 rows=2 loops=5,212)

  • 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
51. 2.981 3.557 ↓ 2,685.2 10,741 1

Sort (cost=0.12..0.13 rows=4 width=150) (actual time=2.596..3.557 rows=10,741 loops=1)

  • Sort Key: tmp.story_id
  • Sort Method: quicksort Memory: 452kB
52. 0.576 0.576 ↓ 1,382.8 5,531 1

CTE Scan on temp_story tmp (cost=0.00..0.08 rows=4 width=150) (actual time=0.001..0.576 rows=5,531 loops=1)

53. 31.269 31.269 ↑ 1.0 1 10,423

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

  • Index Cond: ((story_id)::text = (tmp.story_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. 17.998 17.998 ↓ 0.0 0 8,999

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

  • Index Cond: (((cmn_search_tbl.object_id)::text = (object_id)::text) AND (lower((user_id)::text) = 'zicc232'::text))
55. 0.041 47.160 ↓ 3.3 20 1

GroupAggregate (cost=4,164.00..4,164.10 rows=6 width=13) (actual time=47.100..47.160 rows=20 loops=1)

  • Group Key: story_exec_info_tbl.story_id
56. 0.077 47.119 ↓ 17.7 106 1

Sort (cost=4,164.00..4,164.01 rows=6 width=5) (actual time=47.096..47.119 rows=106 loops=1)

  • Sort Key: story_exec_info_tbl.story_id
  • Sort Method: quicksort Memory: 29kB
57. 0.993 47.042 ↓ 17.7 106 1

Hash Semi Join (cost=0.13..4,163.92 rows=6 width=5) (actual time=39.594..47.042 rows=106 loops=1)

  • Hash Cond: ((story_exec_info_tbl.story_id)::text = (temp_story_2.story_id)::text)
58. 44.395 44.395 ↓ 1.1 11,992 1

Seq Scan on story_exec_info_tbl (cost=0.00..4,135.06 rows=10,919 width=5) (actual time=37.924..44.395 rows=11,992 loops=1)

  • Filter: ((executed_time <= CURRENT_TIMESTAMP) AND (executed_time >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 99958
59. 0.959 1.654 ↓ 1,382.8 5,531 1

Hash (cost=0.08..0.08 rows=4 width=118) (actual time=1.653..1.654 rows=5,531 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 282kB
60. 0.695 0.695 ↓ 1,382.8 5,531 1

CTE Scan on temp_story temp_story_2 (cost=0.00..0.08 rows=4 width=118) (actual time=0.001..0.695 rows=5,531 loops=1)

61. 3,016.099 3,068.659 ↓ 152.6 2,746 8,999

HashAggregate (cost=24.39..24.57 rows=18 width=30) (actual time=0.007..0.341 rows=2,746 loops=8,999)

  • Group Key: story_report_map.story_id
62. 5.275 52.560 ↓ 1,252.8 22,550 1

Nested Loop (cost=0.51..24.21 rows=18 width=92) (actual time=2.106..52.560 rows=22,550 loops=1)

63. 2.474 3.037 ↓ 1,382.8 5,531 1

HashAggregate (cost=0.09..0.13 rows=4 width=118) (actual time=2.082..3.037 rows=5,531 loops=1)

  • Group Key: (temp_story_3.story_id)::text
64. 0.563 0.563 ↓ 1,382.8 5,531 1

CTE Scan on temp_story temp_story_3 (cost=0.00..0.08 rows=4 width=118) (actual time=0.001..0.563 rows=5,531 loops=1)

65. 44.248 44.248 ↑ 1.0 4 5,531

Index Scan using story_report_map_index on story_report_map (cost=0.42..5.98 rows=4 width=92) (actual time=0.006..0.008 rows=4 loops=5,531)

  • Index Cond: ((story_id)::text = (temp_story_3.story_id)::text)
66. 26.997 26.997 ↓ 0.0 0 8,999

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

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