explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SsB7 : outer

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

Nested Loop (cost=4,591,043.63..4,591,822.38 rows=330 width=152) (actual rows= loops=)

  • Join Filter: ((dd.dt >= (lse.event_ts)::date) AND (dd.dt < (lse.real_end_ts)::date))
2.          

CTE membership_events

3. 0.000 0.000 ↓ 0.0

Unique (cost=2,244,691.71..2,383,722.77 rows=3,972,316 width=732) (actual rows= loops=)

  • -> Sort (cost=2244691.71..2254622.50 rows=3972316 width=732)" Sort Key: mcr.id, mcr.event, mcr.subscription_event_id, mcr.subscription_id, mcr.user_id, mcr.received_at, mcr.""timestamp"", mcr.country_id, mcr.country_name, mcr.type, mcr.subtype, mcr.period, ((mcr.""timestamp"" + ((mc
4. 0.000 0.000 ↓ 0.0

Append (cost=0.00..298,338.51 rows=3,972,316 width=732) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Seq Scan on membership_created mcr (cost=0.00..78,314.86 rows=1,284,735 width=141) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on membership_converted mc (cost=0.00..23,623.91 rows=339,652 width=151) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on membership_cancelled mca (cost=0.00..26,810.91 rows=475,491 width=148) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on membership_expired mex (cost=0.00..16,183.16 rows=305,316 width=141) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on membership_locked mlo (cost=0.00..12,150.65 rows=215,765 width=143) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on membership_marked_for_cancellation mmc (cost=0.00..10,907.28 rows=151,502 width=164) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on membership_marked_for_pause mmp (cost=0.00..2,900.23 rows=39,156 width=157) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on membership_paused mpa (cost=0.00..2,184.19 rows=34,519 width=147) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on membership_renewed mre (cost=0.00..83,260.98 rows=1,093,999 width=148) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on membership_restored_cancellation mrc (cost=0.00..204.36 rows=2,649 width=163) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on membership_unmarked_for_pause mup (cost=0.00..104.01 rows=1,372 width=160) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on membership_unpaused munp (cost=0.00..1,970.80 rows=28,160 width=149) (actual rows= loops=)

17.          

CTE membership_events_ranked

18. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,025,799.52..2,105,245.84 rows=3,972,316 width=740) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=2,025,799.52..2,035,730.31 rows=3,972,316 width=732) (actual rows= loops=)

  • Sort Key: me.subscription_event_id, me.event_ts
20. 0.000 0.000 ↓ 0.0

CTE Scan on membership_events me (cost=0.00..79,446.32 rows=3,972,316 width=732) (actual rows= loops=)

21.          

CTE membership_events_deduped

22. 0.000 0.000 ↓ 0.0

CTE Scan on membership_events_ranked mer (cost=0.00..89,377.11 rows=19,862 width=740) (actual rows= loops=)

  • Filter: (rown = 1)
23.          

CTE membership_events_timeframe_fixed

24. 0.000 0.000 ↓ 0.0

WindowAgg (cost=5,634.31..6,031.55 rows=19,862 width=748) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=5,634.31..5,683.97 rows=19,862 width=740) (actual rows= loops=)

  • Sort Key: med.subscription_id, med.event_ts
26. 0.000 0.000 ↓ 0.0

CTE Scan on membership_events_deduped med (cost=0.00..397.24 rows=19,862 width=740) (actual rows= loops=)

27.          

CTE membership_events_timeframe_fixed_rown_pd

28. 0.000 0.000 ↓ 0.0

WindowAgg (cost=5,722.92..6,219.47 rows=19,862 width=760) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Sort (cost=5,722.92..5,772.57 rows=19,862 width=752) (actual rows= loops=)

  • Sort Key: met.subscription_id, ((met.event_ts)::date), met.event_ts DESC
30. 0.000 0.000 ↓ 0.0

CTE Scan on membership_events_timeframe_fixed met (cost=0.00..446.89 rows=19,862 width=752) (actual rows= loops=)

31.          

CTE last_sub_event_pd

32. 0.000 0.000 ↓ 0.0

CTE Scan on membership_events_timeframe_fixed_rown_pd metpd (cost=0.00..446.89 rows=99 width=756) (actual rows= loops=)

  • Filter: (rownb = 1)
33. 0.000 0.000 ↓ 0.0

CTE Scan on last_sub_event_pd lse (cost=0.00..1.98 rows=99 width=160) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..710.02 rows=30 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on dim_date dd (cost=0.00..709.88 rows=30 width=8) (actual rows= loops=)