explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4HgL

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on suggs_at_geo (cost=11,856.51..11,856.63 rows=1 width=708) (actual rows= loops=)

2.          

CTE filtered_leaf_geos

3. 0.000 0.000 ↓ 0.0

Unique (cost=11,815.49..11,817.07 rows=158 width=167) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=11,815.49..11,815.89 rows=158 width=167) (actual rows= loops=)

  • Sort Key: geo_closure_leaf.descendant, (COALESCE(geography_closure_cte.id, '0'::character varying)), parent_info.name
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=7,084.73..11,809.72 rows=158 width=167) (actual rows= loops=)

  • Hash Cond: (((COALESCE(geography_closure_cte.parent_id, '0'::character varying)))::text = (geo_closure_leaf.descendant)::text)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=7,069.30..11,791.72 rows=263 width=81) (actual rows= loops=)

  • Hash Cond: (((COALESCE(geography_closure_cte.id, '0'::character varying)))::text = (parent_info.id)::text)
7. 0.000 0.000 ↓ 0.0

CTE Scan on geography_closure_cte (cost=7,057.42..9,950.60 rows=144,659 width=580) (actual rows= loops=)

8.          

CTE geography_closure_cte

9. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.00..7,057.42 rows=144,659 width=527) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on geography_hierarchy (cost=0.00..10.49 rows=549 width=20) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash Join (cost=17.35..415.38 rows=14,411 width=527) (actual rows= loops=)

  • Hash Cond: ((cte.parent_id)::text = (gh.parent_id)::text)
12. 0.000 0.000 ↓ 0.0

WorkTable Scan on geography_closure_cte cte (cost=0.00..109.80 rows=5,490 width=1,032) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=10.49..10.49 rows=549 width=20) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on geography_hierarchy gh (cost=0.00..10.49 rows=549 width=20) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=11.86..11.86 rows=1 width=26) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on geography_hierarchy parent_info (cost=0.00..11.86 rows=1 width=26) (actual rows= loops=)

  • Filter: ((geography_level_id)::text = ''::text)
17. 0.000 0.000 ↓ 0.0

Hash (cost=13.93..13.93 rows=120 width=118) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on geo_closure_leaf (cost=1.53..13.93 rows=120 width=118) (actual rows= loops=)

  • Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
19.          

SubPlan (forSeq Scan)

20. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

24.          

CTE curr

25. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=19.65..19.68 rows=1 width=580) (actual rows= loops=)

  • Group Key: filtered_leaf_geos.parent_id, filtered_leaf_geos.name
26. 0.000 0.000 ↓ 0.0

Sort (cost=19.65..19.65 rows=1 width=682) (actual rows= loops=)

  • Sort Key: filtered_leaf_geos.parent_id, filtered_leaf_geos.name
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=15.87..19.63 rows=1 width=682) (actual rows= loops=)

  • Hash Cond: ((filtered_leaf_geos.leaf_geo_id)::text = (rep_feedback_summary.geo_id)::text)
28. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_leaf_geos (cost=0.00..3.16 rows=158 width=666) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=15.86..15.86 rows=1 width=352) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using ix_rep_feedback_summary on rep_feedback_summary (cost=7.81..15.86 rows=1 width=352) (actual rows= loops=)

  • Index Cond: ((business_unit_sk)::text = ''::text)
  • Filter: ((sugg_posted_month_recency >= 1) AND (sugg_posted_month_recency <= 3) AND ((hashed SubPlan 5) OR (hashed SubPlan 6)) AND ((hashed SubPlan 7) OR (hashed SubPlan 8)) AND ((hashed SubPlan 9) OR (hashed SubPlan 10)) AND ((hashed SubPlan 11) OR (hashed SubPlan 12)) AND ((hashed SubPlan 13) OR (hashed SubPlan 14)))
31.          

SubPlan (forIndex Scan)

32. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

52.          

CTE prev

53. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=19.65..19.68 rows=1 width=64) (actual rows= loops=)

  • Group Key: b.parent_id
54. 0.000 0.000 ↓ 0.0

Sort (cost=19.65..19.65 rows=1 width=166) (actual rows= loops=)

  • Sort Key: b.parent_id
55. 0.000 0.000 ↓ 0.0

Hash Join (cost=15.87..19.63 rows=1 width=166) (actual rows= loops=)

  • Hash Cond: ((b.leaf_geo_id)::text = (a.geo_id)::text)
56. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_leaf_geos b (cost=0.00..3.16 rows=158 width=150) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=15.86..15.86 rows=1 width=352) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Index Scan using ix_rep_feedback_summary on rep_feedback_summary a (cost=7.81..15.86 rows=1 width=352) (actual rows= loops=)

  • Index Cond: ((business_unit_sk)::text = ''::text)
  • Filter: ((sugg_posted_month_recency >= 4) AND (sugg_posted_month_recency <= 6) AND ((hashed SubPlan 16) OR (hashed SubPlan 17)) AND ((hashed SubPlan 18) OR (hashed SubPlan 19)) AND ((hashed SubPlan 20) OR (hashed SubPlan 21)) AND ((hashed SubPlan 22) OR (hashed SubPlan 23)) AND ((hashed SubPlan 24) OR (hashed SubPlan 25)))
59.          

SubPlan (forIndex Scan)

60. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

80.          

CTE suggs_at_geo

81. 0.000 0.000 ↓ 0.0

Hash Full Join (cost=0.03..0.07 rows=1 width=612) (actual rows= loops=)

  • Hash Cond: ((curr.parent_id)::text = (prev.parent_id)::text)
82. 0.000 0.000 ↓ 0.0

CTE Scan on curr (cost=0.00..0.02 rows=1 width=580) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=64) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

CTE Scan on prev (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)