explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4F1D

Settings
# exclusive inclusive rows x rows loops node
1. 0.220 1,104.186 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=134,475.48..134,477.52 rows=1 width=598) (actual time=1,104.165..1,104.186 rows=1 loops=1)

2.          

CTE overloadedusers

3. 252.895 795.471 ↓ 0.0 0 1

HashAggregate (cost=50,603.26..51,194.61 rows=15,769 width=31) (actual time=795.469..795.471 rows=0 loops=1)

  • Group Key: zoom_pool_user_registration.zoom_user_fk
  • Filter: (count(*) >= 100)
  • Rows Removed by Filter: 49,985
4. 542.576 542.576 ↓ 1.1 497,227 1

Seq Scan on zoom_pool_user_registration (cost=0.00..47,209.13 rows=452,551 width=23) (actual time=0.030..542.576 rows=497,227 loops=1)

  • Filter: (create_date > (now() - '1 day'::interval))
  • Rows Removed by Filter: 317,757
5.          

CTE notavailableusers

6. 0.328 249.529 ↓ 0.0 0 1

HashAggregate (cost=56,204.50..56,671.81 rows=46,731 width=23) (actual time=249.525..249.529 rows=0 loops=1)

  • Group Key: r.zoom_user_fk
7. 0.005 249.201 ↓ 0.0 0 1

Hash Join (cost=10,180.58..56,087.67 rows=46,731 width=23) (actual time=249.199..249.201 rows=0 loops=1)

  • Hash Cond: ((r.zoom_user_fk)::text = (u.zoom_user_id)::text)
8. 249.196 249.196 ↓ 0.0 0 1

Seq Scan on zoom_pool_user_registration r (cost=0.00..45,291.54 rows=234,474 width=23) (actual time=249.196..249.196 rows=0 loops=1)

  • Filter: ((status <> 2) AND "overlaps"(registration_start_date, registration_end_date, '2020-10-02 20:20:00'::timestamp without time zone, '2020-10-02 20:50:00'::timestamp without time zone))
  • Rows Removed by Filter: 814,984
9. 0.000 0.000 ↓ 0.0 0

Hash (cost=10,056.02..10,056.02 rows=9,965 width=23) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on zoom_pool_user u (cost=899.78..10,056.02 rows=9,965 width=23) (never executed)

  • Recheck Cond: (pool_group_level = 5)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on zoom_pool_user_status_group_level_idx (cost=0.00..897.29 rows=9,965 width=0) (never executed)

  • Index Cond: (pool_group_level = 5)
12.          

CTE pool_user

13. 0.137 1,103.750 ↑ 1.0 1 1

Limit (cost=26,598.04..26,602.18 rows=1 width=23) (actual time=1,103.743..1,103.750 rows=1 loops=1)

14.          

Initplan (for Limit)

15. 0.644 16.419 ↑ 1.0 1 1

Aggregate (cost=10,105.83..10,105.84 rows=1 width=8) (actual time=16.418..16.419 rows=1 loops=1)

16. 12.118 15.775 ↓ 1.0 10,017 1

Bitmap Heap Scan on zoom_pool_user (cost=899.78..10,080.93 rows=9,961 width=0) (actual time=4.357..15.775 rows=10,017 loops=1)

  • Recheck Cond: (pool_group_level = 5)
  • Filter: (status <> 1)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=4,034
17. 3.657 3.657 ↓ 1.0 10,022 1

Bitmap Index Scan on zoom_pool_user_status_group_level_idx (cost=0.00..897.29 rows=9,965 width=0) (actual time=3.656..3.657 rows=10,022 loops=1)

  • Index Cond: (pool_group_level = 5)
18. 0.007 249.536 ↑ 1.0 1 1

Aggregate (cost=1,051.45..1,051.46 rows=1 width=8) (actual time=249.535..249.536 rows=1 loops=1)

19. 249.529 249.529 ↓ 0.0 0 1

CTE Scan on notavailableusers (cost=0.00..934.62 rows=46,731 width=0) (actual time=249.529..249.529 rows=0 loops=1)

20. 0.005 795.480 ↑ 1.0 1 1

Aggregate (cost=354.80..354.81 rows=1 width=8) (actual time=795.479..795.480 rows=1 loops=1)

21. 795.475 795.475 ↓ 0.0 0 1

CTE Scan on overloadedusers (cost=0.00..315.38 rows=15,769 width=0) (actual time=795.475..795.475 rows=0 loops=1)

22. 0.895 42.178 ↑ 1.7 2,014 1

Hash Anti Join (cost=13,676.06..27,770.65 rows=3,409 width=23) (actual time=41.272..42.178 rows=2,014 loops=1)

  • Hash Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
23. 0.268 41.281 ↑ 3.4 2,014 1

Merge Anti Join (cost=12,157.30..12,317.37 rows=6,819 width=23) (actual time=40.826..41.281 rows=2,014 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
24. 24.365 40.998 ↑ 4.9 2,014 1

Sort (cost=10,742.44..10,767.34 rows=9,961 width=23) (actual time=40.805..40.998 rows=2,014 loops=1)

  • Sort Key: zpu.zoom_user_id
  • Sort Method: quicksort Memory: 1,167kB
25. 13.029 16.633 ↓ 1.0 10,017 1

Bitmap Heap Scan on zoom_pool_user zpu (cost=899.78..10,080.93 rows=9,961 width=23) (actual time=4.292..16.633 rows=10,017 loops=1)

  • Recheck Cond: (pool_group_level = 5)
  • Filter: (status <> 1)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=4,034
26. 3.604 3.604 ↓ 1.0 10,023 1

Bitmap Index Scan on zoom_pool_user_status_group_level_idx (cost=0.00..897.29 rows=9,965 width=0) (actual time=3.604..3.604 rows=10,023 loops=1)

  • Index Cond: (pool_group_level = 5)
27. 0.012 0.015 ↓ 0.0 0 1

Sort (cost=1,414.86..1,454.28 rows=15,769 width=82) (actual time=0.015..0.015 rows=0 loops=1)

  • Sort Key: ov.zoom_user_fk
  • Sort Method: quicksort Memory: 25kB
28. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on overloadedusers ov (cost=0.00..315.38 rows=15,769 width=82) (actual time=0.003..0.003 rows=0 loops=1)

29. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=934.62..934.62 rows=46,731 width=82) (actual time=0.001..0.002 rows=0 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 512kB
30. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on notavailableusers na (cost=0.00..934.62 rows=46,731 width=82) (actual time=0.001..0.001 rows=0 loops=1)

31.          

CTE real_pool

32. 0.000 1,103.911 ↑ 1.0 1 1

LockRows (cost=2.44..4.47 rows=1 width=29) (actual time=1,103.908..1,103.911 rows=1 loops=1)

33.          

Initplan (for LockRows)

34. 1,103.748 1,103.748 ↑ 1.0 1 1

CTE Scan on pool_user (cost=0.00..0.02 rows=1 width=82) (actual time=1,103.747..1,103.748 rows=1 loops=1)

35. 0.024 1,103.835 ↑ 1.0 1 1

Bitmap Heap Scan on zoom_pool_user p (cost=2.42..4.44 rows=1 width=29) (actual time=1,103.834..1,103.835 rows=1 loops=1)

  • Recheck Cond: ((zoom_user_id)::text = ($6)::text)
  • Filter: (status <> 1)
  • Heap Blocks: exact=1
36. 1,103.811 1,103.811 ↑ 1.0 1 1

Bitmap Index Scan on zoom_user_id_uq (cost=0.00..2.42 rows=1 width=0) (actual time=1,103.811..1,103.811 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = ($6)::text)
37. 0.023 1,103.966 ↑ 1.0 1 1

Nested Loop (cost=2.42..4.46 rows=1 width=598) (actual time=1,103.961..1,103.966 rows=1 loops=1)

38. 1,103.926 1,103.926 ↑ 1.0 1 1

CTE Scan on real_pool (cost=0.00..0.02 rows=1 width=188) (actual time=1,103.923..1,103.926 rows=1 loops=1)

39. 0.007 0.017 ↑ 1.0 1 1

Bitmap Heap Scan on zoom_pool_user uzpu (cost=2.42..4.44 rows=1 width=480) (actual time=0.017..0.017 rows=1 loops=1)

  • Recheck Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
  • Filter: (status <> 1)
  • Heap Blocks: exact=1
40. 0.010 0.010 ↑ 1.0 1 1

Bitmap Index Scan on zoom_user_id_uq (cost=0.00..2.42 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
Planning time : 2.164 ms
Execution time : 1,107.345 ms