explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fQFw

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

GroupAggregate (cost=122,734,300.94..124,268,186.47 rows=25,043,029 width=150) (actual rows= loops=)

  • Group Key: base.seller_id, ((date_trunc('MONTH'::text, base.created_timestamp))::date), base.event_type, historic_counts.event_counts_prior_months
2.          

CTE historic_counts

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=57,530,349.55..60,299,836.32 rows=19,170 width=130) (actual rows= loops=)

  • Group Key: billable_event.seller_id, billable_event_type.description
4. 0.000 0.000 ↓ 0.0

Sort (cost=57,530,349.55..57,807,279.05 rows=110,771,803 width=169) (actual rows= loops=)

  • Sort Key: billable_event.seller_id, billable_event_type.description
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.14..4,868,106.53 rows=110,771,803 width=169) (actual rows= loops=)

  • Hash Cond: (billable_event.billable_event_type_code = billable_event_type.billable_event_type_code)
6. 0.000 0.000 ↓ 0.0

Seq Scan on billable_event (cost=0.00..4,371,940.03 rows=110,771,803 width=51) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=6 width=122) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on billable_event_type (cost=0.00..1.06 rows=6 width=122) (actual rows= loops=)

9.          

CTE past_tracking_numbers

10. 0.000 0.000 ↓ 0.0

Unique (cost=16,136,681.38..16,484,387.82 rows=11,028,232 width=55) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=16,136,681.38..16,252,583.52 rows=46,360,859 width=55) (actual rows= loops=)

  • Sort Key: billable_event_1.seller_id, billable_event_1.tracking_number
12. 0.000 0.000 ↓ 0.0

Seq Scan on billable_event billable_event_1 (cost=0.00..5,479,658.06 rows=46,360,859 width=55) (actual rows= loops=)

  • Filter: ((billable_event_type_code = 5) AND (created_timestamp < date_trunc('MONTH'::text, ('2019-12-01'::date)::timestamp with time zone)))
13.          

CTE base

14. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=19,413,879.57..19,794,213.21 rows=25,043,029 width=169) (actual rows= loops=)

  • Merge Cond: ((billable_event_2.seller_id = ptn.seller_id) AND ((billable_event_2.tracking_number)::text = (ptn.past_tracking)::text) AND ((billable_event_type_1.description)::text = ptn.event_type))
15. 0.000 0.000 ↓ 0.0

Sort (cost=15,302,401.64..15,369,911.86 rows=27,004,088 width=169) (actual rows= loops=)

  • Sort Key: billable_event_2.seller_id, billable_event_2.tracking_number, billable_event_type_1.description
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.14..5,046,755.99 rows=27,004,088 width=169) (actual rows= loops=)

  • Hash Cond: (billable_event_2.billable_event_type_code = billable_event_type_1.billable_event_type_code)
17. 0.000 0.000 ↓ 0.0

Seq Scan on billable_event billable_event_2 (cost=0.00..4,925,799.04 rows=27,004,088 width=55) (actual rows= loops=)

  • Filter: ((created_timestamp >= '2019-12-01 00:00:00'::timestamp without time zone) AND (created_timestamp < '2020-01-01 00:00:00'::timestamp without time zone))
18. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=6 width=122) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on billable_event_type billable_event_type_1 (cost=0.00..1.06 rows=6 width=122) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Sort (cost=4,111,477.94..4,139,048.52 rows=11,028,232 width=154) (actual rows= loops=)

  • Sort Key: ptn.seller_id, ptn.past_tracking, ptn.event_type
21. 0.000 0.000 ↓ 0.0

CTE Scan on past_tracking_numbers ptn (cost=0.00..220,564.64 rows=11,028,232 width=154) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=26,155,863.59..26,218,471.16 rows=25,043,029 width=272) (actual rows= loops=)

  • Sort Key: base.seller_id, ((date_trunc('MONTH'::text, base.created_timestamp))::date), base.event_type, historic_counts.event_counts_prior_months
23. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=13,081,331.63..13,577,139.56 rows=25,043,029 width=272) (actual rows= loops=)

  • Merge Cond: ((historic_counts.seller_id = base.seller_id) AND ((historic_counts.event_type)::text = (base.event_type)::text))
24. 0.000 0.000 ↓ 0.0

Sort (cost=1,747.02..1,794.94 rows=19,170 width=130) (actual rows= loops=)

  • Sort Key: historic_counts.seller_id, historic_counts.event_type
25. 0.000 0.000 ↓ 0.0

CTE Scan on historic_counts (cost=0.00..383.40 rows=19,170 width=130) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Materialize (cost=13,079,584.61..13,204,799.76 rows=25,043,029 width=268) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=13,079,584.61..13,142,192.18 rows=25,043,029 width=268) (actual rows= loops=)

  • Sort Key: base.seller_id, base.event_type
28. 0.000 0.000 ↓ 0.0

CTE Scan on base (cost=0.00..500,860.58 rows=25,043,029 width=268) (actual rows= loops=)