explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OYpR

Settings
# exclusive inclusive rows x rows loops node
1. 880.951 2,719.187 ↑ 2,216.7 12,793 1

Nested Loop Left Join (cost=2,372.34..672,924,855.31 rows=28,357,842 width=1,387) (actual time=54.509..2,719.187 rows=12,793 loops=1)

2. 10.033 1,723.099 ↑ 2,216.7 12,793 1

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

  • Hash Cond: (prog."sourceId" = s.id)
3. 10.514 1,713.006 ↑ 2,216.7 12,793 1

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

  • Hash Cond: (tpp."programId" = prog.id)
4. 11.403 1,702.218 ↑ 2,216.7 12,793 1

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

  • Hash Cond: (c."teamPropertyProgramId" = ctpp.id)
5. 8.624 1,689.360 ↑ 2,216.7 12,793 1

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

  • Hash Cond: (cqs."userId" = cqsu.id)
6. 47.150 1,680.655 ↑ 2,216.7 12,793 1

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

  • Hash Cond: (c.id = cqs."communicationId")
7. 9.130 1,598.758 ↑ 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.288..1,598.758 rows=12,793 loops=1)

  • Hash Cond: (c."userId" = u.id)
8. 337.874 1,589.555 ↑ 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.210..1,589.555 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
9. 207.135 1,251.658 ↑ 242.1 351,727 1

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

10. 32.244 124.543 ↑ 1.0 48,420 1

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

  • Hash Cond: (p."teamPropertyProgramId" = tpp.id)
11. 31.722 92.045 ↑ 1.0 48,420 1

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

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

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

13. 0.541 1.682 ↓ 1.0 1,735 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 111kB
14. 1.141 1.141 ↓ 1.0 1,735 1

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

  • Filter: ((status)::text = 'executed'::text)
  • Rows Removed by Filter: 1112
15. 0.124 0.254 ↓ 3.1 409 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
16. 0.130 0.130 ↓ 3.1 409 1

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

17. 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
18. 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])
19. 0.006 0.023 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.017 0.017 ↑ 1.0 6 1

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

21. 0.033 0.073 ↑ 1.0 88 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
22. 0.040 0.040 ↑ 1.0 88 1

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

23. 18.147 34.747 ↑ 1.0 39,369 1

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

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

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

25. 0.038 0.081 ↑ 1.0 88 1

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

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

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

27. 0.180 1.455 ↓ 3.1 409 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
28. 0.216 1.275 ↓ 3.1 409 1

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

  • Hash Cond: (ctpp."propertyId" = commprop.id)
29. 0.220 1.048 ↓ 3.1 409 1

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

  • Hash Cond: (cprog."sourceId" = cs.id)
30. 0.225 0.749 ↓ 3.1 409 1

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

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

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

32. 0.133 0.264 ↓ 3.1 409 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
33. 0.131 0.131 ↓ 3.1 409 1

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

34. 0.031 0.079 ↑ 1.0 86 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
35. 0.048 0.048 ↑ 1.0 86 1

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

36. 0.003 0.011 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.008 0.008 ↑ 1.0 6 1

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

38. 0.134 0.274 ↓ 1.0 361 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
39. 0.140 0.140 ↓ 1.0 361 1

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

40. 0.031 0.060 ↑ 1.0 86 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
41. 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)

42. 12.793 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)

43. 102.344 102.344 ↑ 1.0 1 12,793

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

  • Filter: (c.teams @> ARRAY[id])
  • Rows Removed by Filter: 7