explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 19pW

Settings
# exclusive inclusive rows x rows loops node
1. 974.739 18,720.541 ↑ 2,339.2 12,123 1

Hash Left Join (cost=20,496,262.18..46,749,654.50 rows=28,357,842 width=1,419) (actual time=16,266.269..18,720.541 rows=12,123 loops=1)

  • Hash Cond: (c.id = person.id)
2. 92.426 15,506.501 ↑ 2,339.2 12,123 1

Hash Left Join (cost=45,681.83..11,533,099.58 rows=28,357,842 width=1,425) (actual time=14,026.840..15,506.501 rows=12,123 loops=1)

  • Hash Cond: (c.id = team.id)
3. 8.946 2,539.425 ↑ 2,339.2 12,123 1

Hash Left Join (cost=2,427.21..11,415,404.71 rows=28,357,842 width=1,377) (actual time=832.651..2,539.425 rows=12,123 loops=1)

  • Hash Cond: (prog."sourceId" = s.id)
4. 8.987 2,530.417 ↑ 2,339.2 12,123 1

Hash Left Join (cost=2,420.27..11,337,290.06 rows=28,357,842 width=1,380) (actual time=832.583..2,530.417 rows=12,123 loops=1)

  • Hash Cond: (tpp."programId" = prog.id)
5. 10.005 2,521.152 ↑ 2,339.2 12,123 1

Hash Left Join (cost=2,389.24..11,261,935.99 rows=28,357,842 width=1,275) (actual time=832.296..2,521.152 rows=12,123 loops=1)

  • Hash Cond: (c."teamPropertyProgramId" = ctpp.id)
6. 7.811 2,506.733 ↑ 2,339.2 12,123 1

Hash Left Join (cost=2,335.69..11,060,905.74 rows=28,357,842 width=1,167) (actual time=827.875..2,506.733 rows=12,123 loops=1)

  • Hash Cond: (cqs."userId" = cqsu.id)
7. 38.172 2,498.840 ↑ 2,339.2 12,123 1

Hash Left Join (cost=2,321.71..10,984,111.77 rows=28,357,842 width=1,169) (actual time=827.778..2,498.840 rows=12,123 loops=1)

  • Hash Cond: (c.id = cqs."communicationId")
8. 8.290 2,404.857 ↑ 2,339.2 12,123 1

Hash Left Join (cost=174.52..3,208,189.33 rows=28,357,842 width=1,085) (actual time=20.633..2,404.857 rows=12,123 loops=1)

  • Hash Cond: (c."userId" = u.id)
9. 342.509 2,394.578 ↑ 2,339.2 12,123 1

Hash Join (cost=160.54..3,131,282.43 rows=28,357,842 width=1,087) (actual time=18.632..2,394.578 rows=12,123 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: 339527
10. 235.688 2,052.043 ↑ 242.1 351,727 1

Nested Loop (cost=158.40..2,784,278.89 rows=85,153,003 width=1,077) (actual time=9.309..2,052.043 rows=351,727 loops=1)

11. 32.469 170.075 ↑ 1.0 48,420 1

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

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

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

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

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

14. 0.580 6.479 ↓ 1.0 1,735 1

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

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

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

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

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

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

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

18. 1,452.600 1,646.280 ↑ 248.4 7 48,420

Bitmap Heap Scan on "Communication" c (cost=13.53..39.26 rows=1,739 width=660) (actual time=0.012..0.034 rows=7 loops=48,420)

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

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

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

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

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

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

22. 0.043 1.989 ↑ 1.0 88 1

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

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

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

24. 21.588 55.811 ↑ 1.0 39,369 1

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

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

Seq Scan on "CallQueueStatistics" cqs (cost=0.00..1,026.31 rows=39,831 width=100) (actual time=0.482..34.223 rows=39,369 loops=1)

26. 0.037 0.082 ↑ 1.0 88 1

Hash (cost=12.88..12.88 rows=88 width=30) (actual time=0.082..0.082 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.006..0.045 rows=88 loops=1)

28. 0.189 4.414 ↓ 3.1 409 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
29. 0.223 4.225 ↓ 3.1 409 1

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

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

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

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

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

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

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

33. 0.136 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.137 0.137 ↓ 3.1 409 1

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

35. 0.035 1.117 ↑ 1.0 86 1

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

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

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

37. 0.004 0.013 ↑ 1.0 6 1

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

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

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

39. 0.137 0.278 ↓ 1.0 361 1

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

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

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

41. 0.032 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.030 0.030 ↑ 1.0 86 1

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

43. 113.420 12,874.650 ↓ 18.0 249,812 1

Hash (cost=43,080.75..43,080.75 rows=13,910 width=48) (actual time=12,874.650..12,874.650 rows=249,812 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 8 (originally 1) Memory Usage: 3585kB
44. 105.292 12,761.230 ↓ 18.0 249,812 1

Subquery Scan on team (cost=42,663.45..43,080.75 rows=13,910 width=48) (actual time=12,382.028..12,761.230 rows=249,812 loops=1)

45. 181.280 12,655.938 ↓ 18.0 249,812 1

GroupAggregate (cost=42,663.45..42,941.65 rows=13,910 width=48) (actual time=12,382.026..12,655.938 rows=249,812 loops=1)

  • Group Key: c1.id
46. 343.789 12,474.658 ↓ 18.1 251,673 1

Sort (cost=42,663.45..42,698.22 rows=13,910 width=32) (actual time=12,382.010..12,474.658 rows=251,673 loops=1)

  • Sort Key: c1.id
  • Sort Method: external merge Disk: 9168kB
47. 118.479 12,130.869 ↓ 18.1 251,673 1

Nested Loop (cost=36.48..41,706.17 rows=13,910 width=32) (actual time=1.520..12,130.869 rows=251,673 loops=1)

48. 1.318 1.318 ↑ 1.0 8 1

Seq Scan on "Teams" p1 (cost=0.00..23.08 rows=8 width=32) (actual time=1.073..1.318 rows=8 loops=1)

49. 11,969.080 12,011.072 ↓ 18.1 31,459 8

Bitmap Heap Scan on "Communication" c1 (cost=36.48..5,193.00 rows=1,739 width=46) (actual time=7.175..1,501.384 rows=31,459 loops=8)

  • Recheck Cond: (teams @> ARRAY[p1.id])
  • Heap Blocks: exact=89215
50. 41.992 41.992 ↓ 18.1 31,459 8

Bitmap Index Scan on "Communication_teams_gin_idx" (cost=0.00..36.04 rows=1,739 width=0) (actual time=5.249..5.249 rows=31,459 loops=8)

  • Index Cond: (teams @> ARRAY[p1.id])
51. 144.550 2,239.301 ↑ 1.0 347,417 1

Hash (cost=20,443,176.32..20,443,176.32 rows=347,762 width=48) (actual time=2,239.301..2,239.301 rows=347,417 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3171kB
52. 144.197 2,094.751 ↑ 1.0 347,417 1

Subquery Scan on person (cost=19,805,898.11..20,443,176.32 rows=347,762 width=48) (actual time=1,540.112..2,094.751 rows=347,417 loops=1)

53. 266.940 1,950.554 ↑ 1.0 347,417 1

GroupAggregate (cost=19,805,898.11..20,439,698.70 rows=347,762 width=48) (actual time=1,540.110..1,950.554 rows=347,417 loops=1)

  • Group Key: c1_1.id
54. 432.908 1,683.614 ↑ 226.3 370,806 1

Sort (cost=19,805,898.11..20,015,715.97 rows=83,927,142 width=27) (actual time=1,540.098..1,683.614 rows=370,806 loops=1)

  • Sort Key: c1_1.id
  • Sort Method: external merge Disk: 14552kB
55. 192.801 1,250.706 ↑ 226.3 370,806 1

Nested Loop (cost=13.53..2,735,910.36 rows=83,927,142 width=27) (actual time=0.942..1,250.706 rows=370,806 loops=1)

56. 24.432 24.432 ↓ 1.0 49,213 1

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

57. 836.621 1,033.473 ↑ 217.4 8 49,213

Bitmap Heap Scan on "Communication" c1_1 (cost=13.53..39.27 rows=1,739 width=48) (actual time=0.008..0.021 rows=8 loops=49,213)

  • Recheck Cond: (persons @> ARRAY[p1_1.id])
  • Heap Blocks: exact=309249
58. 196.852 196.852 ↑ 217.4 8 49,213

Bitmap Index Scan on "Communication_persons_gin_idx" (cost=0.00..13.09 rows=1,739 width=0) (actual time=0.004..0.004 rows=8 loops=49,213)

  • Index Cond: (persons @> ARRAY[p1_1.id])