explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9nbd

Settings
# exclusive inclusive rows x rows loops node
1. 1,343.653 5,567.625 ↑ 221,666.9 12,793 1

Hash Left Join (cost=9,920,692.45..1,149,288,831.84 rows=2,835,784,200 width=1,419) (actual time=3,788.820..5,567.625 rows=12,793 loops=1)

  • Hash Cond: (c.id = person."communicationId")
2. 20.327 1,862.030 ↑ 2,216.7 12,793 1

Nested Loop Left Join (cost=2,372.34..668,316,705.99 rows=28,357,842 width=1,393) (actual time=64.105..1,862.030 rows=12,793 loops=1)

3. 9.772 1,726.566 ↑ 2,216.7 12,793 1

Hash Left Join (cost=2,349.23..12,258,031.32 rows=28,357,842 width=1,391) (actual time=64.070..1,726.566 rows=12,793 loops=1)

  • Hash Cond: (prog."sourceId" = s.id)
4. 10.253 1,716.732 ↑ 2,216.7 12,793 1

Hash Left Join (cost=2,342.30..12,179,916.67 rows=28,357,842 width=1,394) (actual time=64.002..1,716.732 rows=12,793 loops=1)

  • Hash Cond: (tpp."programId" = prog.id)
5. 11.240 1,706.195 ↑ 2,216.7 12,793 1

Hash Left Join (cost=2,311.27..12,104,562.60 rows=28,357,842 width=1,289) (actual time=63.706..1,706.195 rows=12,793 loops=1)

  • Hash Cond: (c."teamPropertyProgramId" = ctpp.id)
6. 9.002 1,693.586 ↑ 2,216.7 12,793 1

Hash Left Join (cost=2,257.72..11,903,532.36 rows=28,357,842 width=1,181) (actual time=62.330..1,693.586 rows=12,793 loops=1)

  • Hash Cond: (cqs."userId" = cqsu.id)
7. 43.251 1,684.505 ↑ 2,216.7 12,793 1

Hash Left Join (cost=2,243.74..11,826,738.38 rows=28,357,842 width=1,183) (actual time=62.236..1,684.505 rows=12,793 loops=1)

  • Hash Cond: (c.id = cqs."communicationId")
8. 9.037 1,596.897 ↑ 2,216.7 12,793 1

Hash Left Join (cost=174.54..3,829,425.94 rows=28,357,842 width=1,115) (actual time=2.093..1,596.897 rows=12,793 loops=1)

  • Hash Cond: (c."userId" = u.id)
9. 341.039 1,587.785 ↑ 2,216.7 12,793 1

Hash Join (cost=160.56..3,752,519.04 rows=28,357,842 width=1,117) (actual time=2.012..1,587.785 rows=12,793 loops=1)

  • Hash Cond: (p."assignedPropertyId" = prop.id)
  • Join Filter: ((timezone(prop.timezone, c.created_at))::date > ((now())::date + '-1 mons'::interval))
  • Rows Removed by Join Filter: 338857
10. 214.287 1,246.729 ↑ 242.1 351,727 1

Nested Loop (cost=158.43..3,405,515.50 rows=85,153,003 width=1,107) (actual time=1.737..1,246.729 rows=351,727 loops=1)

11. 32.333 112.462 ↑ 1.0 48,420 1

Hash Left Join (cost=144.88..9,832.86 rows=48,972 width=449) (actual time=1.703..112.462 rows=48,420 loops=1)

  • Hash Cond: (p."teamPropertyProgramId" = tpp.id)
12. 31.610 79.867 ↑ 1.0 48,420 1

Hash Left Join (cost=131.95..9,690.05 rows=48,972 width=449) (actual time=1.435..79.867 rows=48,420 loops=1)

  • Hash Cond: (p.id = lease."partyId")
13. 46.831 46.831 ↑ 1.0 48,361 1

Seq Scan on "Party" p (cost=0.00..6,663.72 rows=48,972 width=441) (actual time=0.003..46.831 rows=48,361 loops=1)

14. 0.522 1.426 ↓ 1.0 1,735 1

Hash (cost=110.36..110.36 rows=1,727 width=24) (actual time=1.425..1.426 rows=1,735 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 111kB
15. 0.904 0.904 ↓ 1.0 1,735 1

Seq Scan on "Lease" lease (cost=0.00..110.36 rows=1,727 width=24) (actual time=0.005..0.904 rows=1,735 loops=1)

  • Filter: ((status)::text = 'executed'::text)
  • Rows Removed by Filter: 1112
16. 0.128 0.262 ↓ 3.1 409 1

Hash (cost=11.30..11.30 rows=130 width=32) (actual time=0.262..0.262 rows=409 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
17. 0.134 0.134 ↓ 3.1 409 1

Seq Scan on "TeamPropertyProgram" tpp (cost=0.00..11.30 rows=130 width=32) (actual time=0.005..0.134 rows=409 loops=1)

18. 581.040 919.980 ↑ 248.4 7 48,420

Bitmap Heap Scan on "Communication" c (cost=13.55..51.95 rows=1,739 width=719) (actual time=0.010..0.019 rows=7 loops=48,420)

  • Recheck Cond: (parties @> ARRAY[(p.id)::character varying])
  • Heap Blocks: exact=286577
19. 338.940 338.940 ↑ 248.4 7 48,420

Bitmap Index Scan on communication_parties_gin_idx (cost=0.00..13.12 rows=1,739 width=0) (actual time=0.007..0.007 rows=7 loops=48,420)

  • Index Cond: (parties @> ARRAY[(p.id)::character varying])
20. 0.006 0.017 ↑ 1.0 6 1

Hash (cost=2.06..2.06 rows=6 width=42) (actual time=0.017..0.017 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.011 0.011 ↑ 1.0 6 1

Seq Scan on "Property" prop (cost=0.00..2.06 rows=6 width=42) (actual time=0.006..0.011 rows=6 loops=1)

22. 0.032 0.075 ↑ 1.0 88 1

Hash (cost=12.88..12.88 rows=88 width=30) (actual time=0.075..0.075 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.043 0.043 ↑ 1.0 88 1

Seq Scan on "Users" u (cost=0.00..12.88 rows=88 width=30) (actual time=0.003..0.043 rows=88 loops=1)

24. 23.047 44.357 ↑ 1.0 39,369 1

Hash (cost=1,026.31..1,026.31 rows=39,831 width=84) (actual time=44.357..44.357 rows=39,369 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2233kB
25. 21.310 21.310 ↑ 1.0 39,369 1

Seq Scan on "CallQueueStatistics" cqs (cost=0.00..1,026.31 rows=39,831 width=84) (actual time=0.005..21.310 rows=39,369 loops=1)

26. 0.034 0.079 ↑ 1.0 88 1

Hash (cost=12.88..12.88 rows=88 width=30) (actual time=0.079..0.079 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
27. 0.045 0.045 ↑ 1.0 88 1

Seq Scan on "Users" cqsu (cost=0.00..12.88 rows=88 width=30) (actual time=0.005..0.045 rows=88 loops=1)

28. 0.184 1.369 ↓ 3.1 409 1

Hash (cost=51.92..51.92 rows=130 width=140) (actual time=1.369..1.369 rows=409 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
29. 0.212 1.185 ↓ 3.1 409 1

Hash Left Join (cost=21.99..51.92 rows=130 width=140) (actual time=0.375..1.185 rows=409 loops=1)

  • Hash Cond: (ctpp."propertyId" = commprop.id)
30. 0.218 0.962 ↓ 3.1 409 1

Hash Left Join (cost=19.86..49.43 rows=130 width=150) (actual time=0.358..0.962 rows=409 loops=1)

  • Hash Cond: (cprog."sourceId" = cs.id)
31. 0.238 0.676 ↓ 3.1 409 1

Hash Right Join (cost=12.93..42.13 rows=130 width=153) (actual time=0.284..0.676 rows=409 loops=1)

  • Hash Cond: (cprog.id = ctpp."programId")
32. 0.165 0.165 ↓ 1.0 361 1

Seq Scan on "Programs" cprog (cost=0.00..26.57 rows=357 width=137) (actual time=0.004..0.165 rows=361 loops=1)

33. 0.138 0.273 ↓ 3.1 409 1

Hash (cost=11.30..11.30 rows=130 width=48) (actual time=0.273..0.273 rows=409 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
34. 0.135 0.135 ↓ 3.1 409 1

Seq Scan on "TeamPropertyProgram" ctpp (cost=0.00..11.30 rows=130 width=48) (actual time=0.005..0.135 rows=409 loops=1)

35. 0.033 0.068 ↑ 1.0 86 1

Hash (cost=5.86..5.86 rows=86 width=29) (actual time=0.068..0.068 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
36. 0.035 0.035 ↑ 1.0 86 1

Seq Scan on "Sources" cs (cost=0.00..5.86 rows=86 width=29) (actual time=0.005..0.035 rows=86 loops=1)

37. 0.004 0.011 ↑ 1.0 6 1

Hash (cost=2.06..2.06 rows=6 width=22) (actual time=0.010..0.011 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on "Property" commprop (cost=0.00..2.06 rows=6 width=22) (actual time=0.003..0.007 rows=6 loops=1)

39. 0.145 0.284 ↓ 1.0 361 1

Hash (cost=26.57..26.57 rows=357 width=137) (actual time=0.284..0.284 rows=361 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
40. 0.139 0.139 ↓ 1.0 361 1

Seq Scan on "Programs" prog (cost=0.00..26.57 rows=357 width=137) (actual time=0.003..0.139 rows=361 loops=1)

41. 0.033 0.062 ↑ 1.0 86 1

Hash (cost=5.86..5.86 rows=86 width=29) (actual time=0.062..0.062 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
42. 0.029 0.029 ↑ 1.0 86 1

Seq Scan on "Sources" s (cost=0.00..5.86 rows=86 width=29) (actual time=0.003..0.029 rows=86 loops=1)

43. 25.586 115.137 ↑ 1.0 1 12,793

Aggregate (cost=23.11..23.12 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=12,793)

44. 89.551 89.551 ↑ 1.0 1 12,793

Seq Scan on "Teams" t (cost=0.00..23.10 rows=1 width=16) (actual time=0.005..0.007 rows=1 loops=12,793)

  • Filter: (c.teams @> ARRAY[id])
  • Rows Removed by Filter: 7
45. 446.539 2,361.942 ↑ 100.1 347,417 1

Hash (cost=9,177,966.61..9,177,966.61 rows=34,776,200 width=48) (actual time=2,361.941..2,361.942 rows=347,417 loops=1)

  • Buckets: 65536 Batches: 1024 Memory Usage: 534kB
46. 149.071 1,915.403 ↑ 100.1 347,417 1

Subquery Scan on person (cost=8,134,680.61..9,177,966.61 rows=34,776,200 width=48) (actual time=1,343.273..1,915.403 rows=347,417 loops=1)

47. 273.161 1,766.332 ↑ 100.1 347,417 1

GroupAggregate (cost=8,134,680.61..8,830,204.61 rows=34,776,200 width=48) (actual time=1,343.272..1,766.332 rows=347,417 loops=1)

  • Group Key: "Communication".id
48. 429.306 1,493.171 ↑ 93.3 372,576 1

Sort (cost=8,134,680.61..8,221,621.11 rows=34,776,200 width=27) (actual time=1,343.260..1,493.171 rows=372,576 loops=1)

  • Sort Key: "Communication".id
  • Sort Method: external merge Disk: 14616kB
49. 230.886 1,063.865 ↑ 93.3 372,576 1

Hash Join (cost=1,861.01..1,282,539.88 rows=34,776,200 width=27) (actual time=32.095..1,063.865 rows=372,576 loops=1)

  • Hash Cond: ((((unnest("Communication".persons)))::uuid) = p1.id)
50. 319.129 800.977 ↑ 93.3 372,576 1

Result (cost=0.00..841,621.33 rows=34,776,200 width=32) (actual time=0.014..800.977 rows=372,576 loops=1)

51. 298.461 481.848 ↑ 93.3 372,576 1

ProjectSet (cost=0.00..233,037.84 rows=34,776,200 width=48) (actual time=0.009..481.848 rows=372,576 loops=1)

52. 183.387 183.387 ↑ 1.0 347,762 1

Seq Scan on "Communication" (cost=0.00..56,548.62 rows=347,762 width=80) (actual time=0.004..183.387 rows=347,762 loops=1)

53. 17.446 32.002 ↓ 1.0 49,213 1

Hash (cost=1,257.67..1,257.67 rows=48,267 width=27) (actual time=32.001..32.002 rows=49,213 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3385kB
54. 14.556 14.556 ↓ 1.0 49,213 1

Seq Scan on "Person" p1 (cost=0.00..1,257.67 rows=48,267 width=27) (actual time=0.006..14.556 rows=49,213 loops=1)