explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ms2H

Settings
# exclusive inclusive rows x rows loops node
1. 8.259 25,241.544 ↑ 2.4 82 1

GroupAggregate (cost=561,677.70..564,452.16 rows=200 width=36) (actual time=24,356.888..25,241.544 rows=82 loops=1)

  • Group Key: num_seats."timestamp
2.          

CTE mau

3. 28.896 43.209 ↓ 1.9 11,153 1

HashAggregate (cost=1,691.40..1,749.99 rows=5,859 width=41) (actual time=39.768..43.209 rows=11,153 loops=1)

  • Group Key: ld_mau.account_id, ld_mau."timestamp
4. 14.313 14.313 ↑ 1.0 58,594 1

Seq Scan on ld_mau (cost=0.00..1,251.94 rows=58,594 width=33) (actual time=0.018..14.313 rows=58,594 loops=1)

5.          

CTE dau

6. 140.350 198.492 ↓ 3.0 71,214 1

HashAggregate (cost=6,890.43..7,129.14 rows=23,871 width=41) (actual time=168.364..198.492 rows=71,214 loops=1)

  • Group Key: ld_dau.account_id, ld_dau."timestamp
7. 58.142 58.142 ↑ 1.0 238,710 1

Seq Scan on ld_dau (cost=0.00..5,100.10 rows=238,710 width=33) (actual time=0.013..58.142 rows=238,710 loops=1)

8.          

CTE num_seats

9. 608.486 4,873.877 ↓ 8.9 652,014 1

Finalize GroupAggregate (cost=52,680.02..76,831.95 rows=73,649 width=69) (actual time=3,174.333..4,873.877 rows=652,014 loops=1)

  • Group Key: ld_accounts.oid, (date_trunc('day'::text, ld_accounts.imported_at))
10. 0.000 4,265.391 ↓ 4.7 685,928 1

Gather Merge (cost=52,680.02..74,438.36 rows=147,298 width=69) (actual time=3,174.321..4,265.391 rows=685,928 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 836.295 10,835.712 ↓ 3.1 228,643 3

Partial GroupAggregate (cost=51,679.99..56,436.49 rows=73,649 width=69) (actual time=3,105.462..3,611.904 rows=228,643 loops=3)

  • Group Key: ld_accounts.oid, (date_trunc('day'::text, ld_accounts.imported_at))
12. 8,937.564 9,999.417 ↑ 1.3 243,913 3

Sort (cost=51,679.99..52,447.17 rows=306,871 width=47) (actual time=3,105.453..3,333.139 rows=243,913 loops=3)

  • Sort Key: ld_accounts.oid, (date_trunc('day'::text, ld_accounts.imported_at))
  • Sort Method: external merge Disk: 12696kB
13. 1,061.853 1,061.853 ↑ 1.3 243,913 3

Parallel Seq Scan on ld_accounts (cost=0.00..14,269.89 rows=306,871 width=47) (actual time=0.013..353.951 rows=243,913 loops=3)

14.          

CTE num_alloc_seats

15. 3,286.145 10,235.532 ↓ 3.3 652,014 1

GroupAggregate (cost=408,612.13..430,951.85 rows=198,575 width=41) (actual time=6,035.699..10,235.532 rows=652,014 loops=1)

  • Group Key: ld_account_members.account_id, (date_trunc('day'::text, ld_account_members.imported_at))
16. 5,957.776 6,949.387 ↑ 1.0 1,985,409 1

Sort (cost=408,612.13..413,576.52 rows=1,985,753 width=58) (actual time=6,035.671..6,949.387 rows=1,985,409 loops=1)

  • Sort Key: ld_account_members.account_id, (date_trunc('day'::text, ld_account_members.imported_at))
  • Sort Method: external merge Disk: 145808kB
17. 991.611 991.611 ↑ 1.0 1,985,409 1

Seq Scan on ld_account_members (cost=0.00..51,565.91 rows=1,985,753 width=58) (actual time=0.022..991.611 rows=1,985,409 loops=1)

18. 334.720 25,233.285 ↑ 1.2 26,096 1

Merge Join (cost=45,014.77..47,387.72 rows=31,961 width=36) (actual time=24,347.448..25,233.285 rows=26,096 loops=1)

  • Merge Cond: ((num_alloc_seats."timestamp" = num_seats."timestamp") AND ((num_alloc_seats.account_id)::text = dau.account_id))
19. 2,490.600 13,179.538 ↓ 3.2 641,492 1

Sort (cost=30,947.93..31,444.37 rows=198,575 width=82) (actual time=12,780.603..13,179.538 rows=641,492 loops=1)

  • Sort Key: num_alloc_seats."timestamp", num_alloc_seats.account_id
  • Sort Method: external merge Disk: 37072kB
20. 10,688.938 10,688.938 ↓ 3.3 652,014 1

CTE Scan on num_alloc_seats (cost=0.00..3,971.50 rows=198,575 width=82) (actual time=6,035.703..10,688.938 rows=652,014 loops=1)

21. 13.336 11,719.027 ↓ 4.1 26,096 1

Materialize (cost=14,066.84..14,407.26 rows=6,438 width=132) (actual time=11,566.812..11,719.027 rows=26,096 loops=1)

22. 83.175 11,705.691 ↓ 4.1 26,096 1

Merge Join (cost=14,066.84..14,391.16 rows=6,438 width=132) (actual time=11,566.808..11,705.691 rows=26,096 loops=1)

  • Merge Cond: ((num_seats."timestamp" = dau."timestamp") AND (num_seats.account_id = dau.account_id))
23. 355.018 11,170.896 ↓ 8.4 90,835 1

Sort (cost=11,853.65..11,880.62 rows=10,788 width=84) (actual time=11,119.335..11,170.896 rows=90,835 loops=1)

  • Sort Key: num_seats."timestamp", num_seats.account_id
  • Sort Method: external merge Disk: 7472kB
24. 252.286 10,815.878 ↓ 8.5 91,665 1

Merge Join (cost=10,178.75..11,131.00 rows=10,788 width=84) (actual time=10,363.077..10,815.878 rows=91,665 loops=1)

  • Merge Cond: ((num_seats.account_id = mau.account_id) AND ((date_trunc('month'::text, num_seats."timestamp")) = mau."timestamp"))
25. 4,759.028 10,455.508 ↓ 8.9 652,008 1

Sort (cost=9,694.90..9,879.03 rows=73,649 width=44) (actual time=10,277.132..10,455.508 rows=652,008 loops=1)

  • Sort Key: num_seats.account_id, (date_trunc('month'::text, num_seats."timestamp"))
  • Sort Method: external sort Disk: 37008kB
26. 5,696.480 5,696.480 ↓ 8.9 652,014 1

CTE Scan on num_seats (cost=0.00..1,472.98 rows=73,649 width=44) (actual time=3,174.340..5,696.480 rows=652,014 loops=1)

27. 58.450 108.084 ↓ 16.3 95,655 1

Sort (cost=483.85..498.50 rows=5,859 width=48) (actual time=85.749..108.084 rows=95,655 loops=1)

  • Sort Key: mau.account_id, mau."timestamp
  • Sort Method: quicksort Memory: 1256kB
28. 49.634 49.634 ↓ 1.9 11,153 1

CTE Scan on mau (cost=0.00..117.18 rows=5,859 width=48) (actual time=39.771..49.634 rows=11,153 loops=1)

29. 204.773 451.620 ↓ 3.0 71,214 1

Sort (cost=2,213.20..2,272.87 rows=23,871 width=48) (actual time=431.435..451.620 rows=71,214 loops=1)

  • Sort Key: dau."timestamp", dau.account_id
  • Sort Method: external sort Disk: 4328kB
30. 246.847 246.847 ↓ 3.0 71,214 1

CTE Scan on dau (cost=0.00..477.42 rows=23,871 width=48) (actual time=168.369..246.847 rows=71,214 loops=1)