explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MKsR

Settings
# exclusive inclusive rows x rows loops node
1. 1.059 16,325.244 ↑ 6.3 57 1

GroupAggregate (cost=519,448.59..520,878.98 rows=360 width=68) (actual time=16,310.376..16,325.244 rows=57 loops=1)

  • Group Key: num_seats."timestamp", num_seats.account_id
2.          

CTE num_seats

3. 733.376 5,198.219 ↓ 8.9 652,014 1

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

  • Group Key: ld_accounts.oid, (date_trunc('day'::text, ld_accounts.imported_at))
4. 0.000 4,464.843 ↓ 4.6 682,256 1

Gather Merge (cost=52,680.02..74,438.36 rows=147,298 width=69) (actual time=3,217.728..4,464.843 rows=682,256 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 861.534 11,015.799 ↓ 3.1 227,419 3

Partial GroupAggregate (cost=51,679.99..56,436.49 rows=73,649 width=69) (actual time=3,140.618..3,671.933 rows=227,419 loops=3)

  • Group Key: ld_accounts.oid, (date_trunc('day'::text, ld_accounts.imported_at))
6. 9,010.200 10,154.265 ↑ 1.3 243,913 3

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

  • Sort Key: ld_accounts.oid, (date_trunc('day'::text, ld_accounts.imported_at))
  • Sort Method: external merge Disk: 12760kB
7. 1,144.065 1,144.065 ↑ 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..381.355 rows=243,913 loops=3)

8.          

CTE num_alloc_seats

9. 3,286.019 10,146.037 ↓ 3.3 652,014 1

GroupAggregate (cost=408,612.13..430,951.85 rows=198,575 width=41) (actual time=5,947.639..10,146.037 rows=652,014 loops=1)

  • Group Key: ld_account_members.account_id, (date_trunc('day'::text, ld_account_members.imported_at))
10. 5,839.385 6,860.018 ↑ 1.0 1,985,409 1

Sort (cost=408,612.13..413,576.52 rows=1,985,753 width=58) (actual time=5,947.607..6,860.018 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
11. 1,020.633 1,020.633 ↑ 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.009..1,020.633 rows=1,985,409 loops=1)

12. 2.160 16,324.185 ↓ 8.2 3,123 1

Merge Join (cost=11,664.78..13,085.84 rows=382 width=216) (actual time=16,307.316..16,324.185 rows=3,123 loops=1)

  • Merge Cond: (num_seats."timestamp" = num_alloc_seats."timestamp")
13. 7.560 5,803.056 ↓ 40.6 3,123 1

Nested Loop Left Join (cost=7,147.42..8,559.50 rows=77 width=216) (actual time=5,788.985..5,803.056 rows=3,123 loops=1)

  • Join Filter: ((mau.account_id = num_seats.account_id) AND (date_trunc('month'::text, num_seats."timestamp") = mau."timestamp"))
  • Rows Removed by Join Filter: 12477
14. 0.229 5,786.136 ↓ 2.7 208 1

Merge Join (cost=7,147.42..7,150.28 rows=77 width=134) (actual time=5,785.801..5,786.136 rows=208 loops=1)

  • Merge Cond: (dau."timestamp" = num_seats."timestamp")
15. 0.399 27.478 ↓ 6.7 334 1

Sort (cost=5,474.63..5,474.76 rows=50 width=115) (actual time=27.384..27.478 rows=334 loops=1)

  • Sort Key: dau."timestamp
  • Sort Method: quicksort Memory: 113kB
16. 2.021 27.079 ↓ 6.7 334 1

Gather (cost=1,000.00..5,473.22 rows=50 width=115) (actual time=0.322..27.079 rows=334 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
17. 25.058 25.058 ↓ 5.8 167 2

Parallel Seq Scan on ld_dau dau (cost=0.00..4,468.22 rows=29 width=115) (actual time=0.047..25.058 rows=167 loops=2)

  • Filter: (account_id = '5a8e925240a4ed0a0f03219c'::text)
  • Rows Removed by Filter: 119188
18. 0.140 5,758.429 ↑ 1.6 234 1

Sort (cost=1,672.79..1,673.71 rows=368 width=44) (actual time=5,758.379..5,758.429 rows=234 loops=1)

  • Sort Key: num_seats."timestamp
  • Sort Method: quicksort Memory: 31kB
19. 5,758.289 5,758.289 ↑ 4.4 83 1

CTE Scan on num_seats (cost=0.00..1,657.10 rows=368 width=44) (actual time=4,871.031..5,758.289 rows=83 loops=1)

  • Filter: (account_id = '5a8e925240a4ed0a0f03219c'::text)
  • Rows Removed by Filter: 651931
20. 3.295 9.360 ↓ 10.7 75 208

Materialize (cost=0.00..1,398.46 rows=7 width=115) (actual time=0.001..0.045 rows=75 loops=208)

21. 6.065 6.065 ↓ 10.7 75 1

Seq Scan on ld_mau mau (cost=0.00..1,398.43 rows=7 width=115) (actual time=0.059..6.065 rows=75 loops=1)

  • Filter: (account_id = '5a8e925240a4ed0a0f03219c'::text)
  • Rows Removed by Filter: 58519
22. 0.676 10,518.969 ↓ 3.2 3,149 1

Sort (cost=4,517.37..4,519.85 rows=993 width=82) (actual time=10,518.325..10,518.969 rows=3,149 loops=1)

  • Sort Key: num_alloc_seats."timestamp
  • Sort Method: quicksort Memory: 31kB
23. 10,518.293 10,518.293 ↑ 12.0 83 1

CTE Scan on num_alloc_seats (cost=0.00..4,467.94 rows=993 width=82) (actual time=9,602.872..10,518.293 rows=83 loops=1)

  • Filter: ((account_id)::text = '5a8e925240a4ed0a0f03219c'::text)
  • Rows Removed by Filter: 651931