explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1GaA

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 9,423.416 ↑ 4,303.2 14 1

Sort (cost=11,882,586.24..11,882,736.85 rows=60,245 width=188) (actual time=9,423.415..9,423.416 rows=14 loops=1)

  • Sort Key: ((row_number() OVER (?) - 1))
  • Sort Method: quicksort Memory: 28kB
2.          

CTE cohort

3. 3.109 5.836 ↑ 1.5 2,655 1

HashAggregate (cost=2,207.80..2,247.96 rows=4,016 width=16) (actual time=4.882..5.836 rows=2,655 loops=1)

  • Group Key: recharge_subscriptions.recharge_customer_id
4. 2.727 2.727 ↑ 1.0 4,099 1

Index Scan using index_recharge_subscriptions_on_shop_id_and_recharge_id on recharge_subscriptions (cost=0.42..2,187.29 rows=4,103 width=16) (actual time=0.012..2.727 rows=4,099 loops=1)

  • Index Cond: (shop_id = 1,453)
5.          

CTE cohort_days

6. 0.127 0.127 ↑ 10.8 93 1

Function Scan on generate_series dd (cost=0.01..17.51 rows=1,000 width=8) (actual time=0.033..0.127 rows=93 loops=1)

7.          

CTE customer_data

8. 22.978 440.931 ↑ 2.0 29,994 1

Group (cost=16,174.59..19,186.84 rows=60,245 width=68) (actual time=400.637..440.931 rows=29,994 loops=1)

  • Group Key: ((date_trunc('week'::text, (cd_1.cohort_day)::timestamp with time zone))::date), rs.id
9. 170.533 417.953 ↓ 3.1 188,930 1

Sort (cost=16,174.59..16,325.20 rows=60,245 width=56) (actual time=400.632..417.953 rows=188,930 loops=1)

  • Sort Key: ((date_trunc('week'::text, (cd_1.cohort_day)::timestamp with time zone))::date), rs.id
  • Sort Method: quicksort Memory: 32,713kB
10. 189.920 247.420 ↓ 3.1 188,930 1

Nested Loop (cost=96.86..11,391.57 rows=60,245 width=56) (actual time=9.364..247.420 rows=188,930 loops=1)

  • Join Filter: ((rs.recharge_created_at <= cd_1.cohort_day) AND (((rs.recharge_status)::text = 'ACTIVE'::text) OR (((rs.recharge_status)::text = 'CANCELLED'::text) AND (cd_1.cohort_day < rs.cancelled_at)) OR (((rs.recharge_status)::text = 'EXPIRED'::text) AND (cd_1.cohort_day < rs.recharge_updated_at))))
  • Rows Removed by Join Filter: 192,277
11. 0.212 0.212 ↑ 10.8 93 1

CTE Scan on cohort_days cd_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.034..0.212 rows=93 loops=1)

12. 25.944 57.288 ↓ 15.9 4,099 93

Materialize (cost=96.86..3,852.87 rows=257 width=48) (actual time=0.100..0.616 rows=4,099 loops=93)

13. 6.327 31.344 ↓ 15.9 4,099 1

Hash Join (cost=96.86..3,851.59 rows=257 width=48) (actual time=9.315..31.344 rows=4,099 loops=1)

  • Hash Cond: (rs.recharge_customer_id = cohort.recharge_customer_id)
14. 15.716 15.716 ↓ 1.3 68,312 1

Seq Scan on recharge_subscriptions rs (cost=0.00..3,619.46 rows=51,527 width=48) (actual time=0.008..15.716 rows=68,312 loops=1)

  • Filter: (((recharge_status)::text = 'ACTIVE'::text) OR ((recharge_status)::text = 'CANCELLED'::text) OR ((recharge_status)::text = 'EXPIRED'::text))
15. 0.477 9.301 ↓ 13.3 2,655 1

Hash (cost=94.36..94.36 rows=200 width=8) (actual time=9.300..9.301 rows=2,655 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 136kB
16. 1.752 8.824 ↓ 13.3 2,655 1

HashAggregate (cost=90.36..92.36 rows=200 width=8) (actual time=8.345..8.824 rows=2,655 loops=1)

  • Group Key: cohort.recharge_customer_id
17. 7.072 7.072 ↑ 1.5 2,655 1

CTE Scan on cohort (cost=0.00..80.32 rows=4,016 width=8) (actual time=4.884..7.072 rows=2,655 loops=1)

18.          

CTE active_subscriptions

19. 0.541 4,776.080 ↑ 2.2 93 1

WindowAgg (cost=3,251,456.12..4,364,690.96 rows=200 width=100) (actual time=1,915.042..4,776.080 rows=93 loops=1)

20. 2,484.144 4,775.539 ↑ 2.2 93 1

GroupAggregate (cost=3,251,456.12..4,364,687.46 rows=200 width=92) (actual time=1,882.678..4,775.539 rows=93 loops=1)

  • Group Key: 'subscriptions'::text, m.cohort_day_index, m.cohort_day
21. 1,261.958 2,291.395 ↑ 5.5 2,612,133 1

Sort (cost=3,251,456.12..3,287,366.73 rows=14,364,243 width=67) (actual time=1,862.066..2,291.395 rows=2,612,133 loops=1)

  • Sort Key: m.cohort_day_index, m.cohort_day
  • Sort Method: external merge Disk: 168,704kB
22. 765.037 1,029.437 ↑ 5.5 2,612,133 1

Nested Loop (cost=0.00..1,198,757.90 rows=14,364,243 width=67) (actual time=0.014..1,029.437 rows=2,612,133 loops=1)

  • Join Filter: ((m.cohort_day >= subscriptions.recharge_created_at) AND ((subscriptions.cancelled_at IS NULL) OR (m.cohort_day < subscriptions.cancelled_at)))
  • Rows Removed by Join Filter: 3,740,883
23. 0.094 0.094 ↑ 10.8 93 1

CTE Scan on cohort_days m (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.001..0.094 rows=93 loops=1)

24. 245.202 264.306 ↑ 1.0 68,312 93

Materialize (cost=0.00..3,448.68 rows=68,312 width=43) (actual time=0.000..2.842 rows=68,312 loops=93)

25. 19.104 19.104 ↑ 1.0 68,312 1

Seq Scan on recharge_subscriptions subscriptions (cost=0.00..3,107.12 rows=68,312 width=43) (actual time=0.008..19.104 rows=68,312 loops=1)

26.          

CTE new_subscribers

27. 15.608 167.145 ↓ 289.0 289 1

GroupAggregate (cost=3,096,121.38..3,096,121.40 rows=1 width=12) (actual time=145.123..167.145 rows=289 loops=1)

  • Group Key: ((date_trunc('week'::text, s.recharge_created_at))::date)
28. 18.998 151.537 ↓ 54,588.0 54,588 1

Sort (cost=3,096,121.38..3,096,121.38 rows=1 width=12) (actual time=145.106..151.537 rows=54,588 loops=1)

  • Sort Key: ((date_trunc('week'::text, s.recharge_created_at))::date)
  • Sort Method: quicksort Memory: 4,095kB
29. 63.246 132.539 ↓ 54,588.0 54,588 1

Hash Left Join (cost=3,961.02..3,096,121.37 rows=1 width=12) (actual time=28.309..132.539 rows=54,588 loops=1)

  • Hash Cond: (s.recharge_customer_id = s1.recharge_customer_id)
  • Join Filter: (s.recharge_created_at > s1.recharge_created_at)
  • Rows Removed by Join Filter: 138,072
  • Filter: (s1.id IS NULL)
  • Rows Removed by Filter: 42,382
30. 41.094 41.094 ↓ 2.0 68,312 1

Seq Scan on recharge_subscriptions s (cost=0.00..3,089,614.06 rows=34,156 width=16) (actual time=0.016..41.094 rows=68,312 loops=1)

  • Filter: (SubPlan 5)
31.          

SubPlan (for Seq Scan)

32. 0.000 0.000 ↑ 4,016.0 1 68,312

CTE Scan on cohort cohort_1 (cost=0.00..80.32 rows=4,016 width=8) (actual time=0.000..0.000 rows=1 loops=68,312)

33. 12.388 28.199 ↑ 1.0 68,312 1

Hash (cost=3,107.12..3,107.12 rows=68,312 width=24) (actual time=28.198..28.199 rows=68,312 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,760kB
34. 15.811 15.811 ↑ 1.0 68,312 1

Seq Scan on recharge_subscriptions s1 (cost=0.00..3,107.12 rows=68,312 width=24) (actual time=0.005..15.811 rows=68,312 loops=1)

35.          

CTE new_subscribers_total

36. 1,223.601 3,991.039 ↑ 2.2 93 1

GroupAggregate (cost=4,209,339.31..4,380,121.31 rows=200 width=12) (actual time=2,284.771..3,991.039 rows=93 loops=1)

  • Group Key: cd_2.cohort_day
37. 1,521.304 2,767.438 ↑ 4.1 5,581,092 1

Sort (cost=4,209,339.31..4,266,265.97 rows=22,770,667 width=12) (actual time=2,270.078..2,767.438 rows=5,581,092 loops=1)

  • Sort Key: cd_2.cohort_day
  • Sort Method: external merge Disk: 142,000kB
38. 920.330 1,246.134 ↑ 4.1 5,581,092 1

Nested Loop (cost=0.00..1,198,757.90 rows=22,770,667 width=12) (actual time=0.019..1,246.134 rows=5,581,092 loops=1)

  • Join Filter: ((rs_1.recharge_created_at)::date < cd_2.cohort_day)
  • Rows Removed by Join Filter: 771,924
39. 0.118 0.118 ↑ 10.8 93 1

CTE Scan on cohort_days cd_2 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.001..0.118 rows=93 loops=1)

40. 305.047 325.686 ↑ 1.0 68,312 93

Materialize (cost=0.00..3,448.68 rows=68,312 width=16) (actual time=0.000..3.502 rows=68,312 loops=93)

41. 20.639 20.639 ↑ 1.0 68,312 1

Seq Scan on recharge_subscriptions rs_1 (cost=0.00..3,107.12 rows=68,312 width=16) (actual time=0.012..20.639 rows=68,312 loops=1)

42. 0.045 9,423.404 ↑ 4,303.2 14 1

WindowAgg (cost=10,748.25..15,417.24 rows=60,245 width=188) (actual time=9,413.447..9,423.404 rows=14 loops=1)

43. 9.536 9,423.359 ↑ 4,303.2 14 1

GroupAggregate (cost=10,748.25..13,158.05 rows=60,245 width=92) (actual time=9,412.949..9,423.359 rows=14 loops=1)

  • Group Key: cd.cohort_interval, active_subscriptions.mrr, (COALESCE(ns.count, '0'::bigint)), (COALESCE(nst.total_new_subscribers, '0'::bigint)), active_subscriptions.active_subscribers, active_subscriptions.active_subscriptions
44. 10.501 9,413.823 ↑ 2.0 29,994 1

Sort (cost=10,748.25..10,898.87 rows=60,245 width=92) (actual time=9,412.662..9,413.823 rows=29,994 loops=1)

  • Sort Key: cd.cohort_interval, active_subscriptions.mrr, (COALESCE(ns.count, '0'::bigint)), (COALESCE(nst.total_new_subscribers, '0'::bigint)), active_subscriptions.active_subscribers, active_subscriptions.active_subscriptions
  • Sort Method: quicksort Memory: 4,922kB
45. 5.380 9,403.322 ↑ 2.0 29,994 1

Hash Left Join (cost=13.03..5,965.24 rows=60,245 width=92) (actual time=9,335.840..9,403.322 rows=29,994 loops=1)

  • Hash Cond: (cd.cohort_interval = nst.cohort_date)
46. 5.035 5,406.560 ↑ 2.0 29,994 1

Hash Left Join (cost=6.53..3,699.55 rows=60,245 width=84) (actual time=5,344.450..5,406.560 rows=29,994 loops=1)

  • Hash Cond: (cd.cohort_interval = ns.cohort_date)
47. 6.627 5,234.162 ↑ 2.0 29,994 1

Hash Left Join (cost=6.50..3,470.59 rows=60,245 width=76) (actual time=5,177.077..5,234.162 rows=29,994 loops=1)

  • Hash Cond: (cd.cohort_interval = active_subscriptions.cohort_date)
48. 451.106 451.106 ↑ 2.0 29,994 1

CTE Scan on customer_data cd (cost=0.00..1,204.90 rows=60,245 width=28) (actual time=400.639..451.106 rows=29,994 loops=1)

49. 0.154 4,776.429 ↑ 2.2 93 1

Hash (cost=4.00..4.00 rows=200 width=52) (actual time=4,776.429..4,776.429 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
50. 4,776.275 4,776.275 ↑ 2.2 93 1

CTE Scan on active_subscriptions (cost=0.00..4.00 rows=200 width=52) (actual time=1,915.045..4,776.275 rows=93 loops=1)

51. 0.083 167.363 ↓ 289.0 289 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=167.362..167.363 rows=289 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
52. 167.280 167.280 ↓ 289.0 289 1

CTE Scan on new_subscribers ns (cost=0.00..0.02 rows=1 width=12) (actual time=145.125..167.280 rows=289 loops=1)

53. 0.144 3,991.382 ↑ 2.2 93 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=3,991.382..3,991.382 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
54. 3,991.238 3,991.238 ↑ 2.2 93 1

CTE Scan on new_subscribers_total nst (cost=0.00..4.00 rows=200 width=12) (actual time=2,284.774..3,991.238 rows=93 loops=1)

Planning time : 0.956 ms
Execution time : 9,468.616 ms