explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aqig

Settings
# exclusive inclusive rows x rows loops node
1. 877.686 2,709.272 ↑ 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=51.733..2,709.272 rows=12,793 loops=1)

2. 9.942 1,703.656 ↑ 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=51.672..1,703.656 rows=12,793 loops=1)

  • Hash Cond: (prog."sourceId" = s.id)
3. 10.947 1,693.653 ↑ 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=51.605..1,693.653 rows=12,793 loops=1)

  • Hash Cond: (tpp."programId" = prog.id)
4. 11.413 1,682.424 ↑ 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=51.316..1,682.424 rows=12,793 loops=1)

  • Hash Cond: (c."teamPropertyProgramId" = ctpp.id)
5. 8.773 1,669.628 ↑ 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=49.927..1,669.628 rows=12,793 loops=1)

  • Hash Cond: (cqs."userId" = cqsu.id)
6. 46.693 1,660.772 ↑ 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=49.830..1,660.772 rows=12,793 loops=1)

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

  • Hash Cond: (c."userId" = u.id)
8. 339.557 1,572.323 ↑ 2,216.7 12,793 1

Hash Join (cost=160.56..3,752,519.04 rows=28,357,842 width=1,117) (actual time=1.966..1,572.323 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. 200.868 1,232.746 ↑ 242.1 351,727 1

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

10. 32.782 111.898 ↑ 1.0 48,420 1

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

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

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

  • Hash Cond: (p.id = lease."partyId")
12. 45.403 45.403 ↑ 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..45.403 rows=48,361 loops=1)

13. 0.538 1.430 ↓ 1.0 1,735 1

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

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

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

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

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

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

Seq Scan on "TeamPropertyProgram" tpp (cost=0.00..11.30 rows=130 width=32) (actual time=0.004..0.135 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.008 0.020 ↑ 1.0 6 1

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

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

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

21. 0.034 0.076 ↑ 1.0 88 1

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

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

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

23. 18.080 32.502 ↑ 1.0 39,369 1

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

  • Buckets: 32768 Batches: 2 Memory Usage: 2233kB
24. 14.422 14.422 ↑ 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..14.422 rows=39,369 loops=1)

25. 0.037 0.083 ↑ 1.0 88 1

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

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

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

27. 0.186 1.383 ↓ 3.1 409 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
28. 0.217 1.197 ↓ 3.1 409 1

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

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

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

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

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

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

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

32. 0.144 0.280 ↓ 3.1 409 1

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

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

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

34. 0.034 0.071 ↑ 1.0 86 1

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

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

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

36. 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
37. 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)

38. 0.138 0.282 ↓ 1.0 361 1

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

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

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

40. 0.032 0.061 ↑ 1.0 86 1

Hash (cost=5.86..5.86 rows=86 width=29) (actual time=0.061..0.061 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. 25.586 127.930 ↑ 1.0 1 12,793

Aggregate (cost=23.11..23.12 rows=1 width=32) (actual time=0.009..0.010 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