explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rmIV

Settings
# exclusive inclusive rows x rows loops node
1. 0.281 3,730.772 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=455,573.41..455,577.45 rows=1 width=482) (actual time=3,730.751..3,730.772 rows=1 loops=1)

2.          

CTE overloadedusers

3. 487.221 1,152.789 ↓ 0.0 0 1

HashAggregate (cost=141,340.73..141,926.59 rows=15,623 width=31) (actual time=1,152.788..1,152.789 rows=0 loops=1)

  • Group Key: zoom_pool_user_registration.zoom_user_fk
  • Filter: (count(*) >= 100)
  • Rows Removed by Filter: 47,499
4. 665.568 665.568 ↑ 1.0 618,386 1

Index Only Scan using zoom_pool_user_registration_create_date_idx on zoom_pool_user_registration (cost=0.43..136,575.68 rows=635,340 width=23) (actual time=0.239..665.568 rows=618,386 loops=1)

  • Index Cond: (create_date > (now() - '1 day'::interval))
  • Heap Fetches: 618,394
5.          

CTE notavailableusers

6. 32.973 2,359.433 ↑ 1.6 29,477 1

HashAggregate (cost=300,032.70..300,501.39 rows=46,869 width=23) (actual time=2,350.634..2,359.433 rows=29,477 loops=1)

  • Group Key: zoom_pool_user_registration_1.zoom_user_fk
7. 1,211.732 2,326.460 ↑ 32.6 32,522 1

Bitmap Heap Scan on zoom_pool_user_registration zoom_pool_user_registration_1 (cost=130,575.75..297,384.86 rows=1,059,136 width=23) (actual time=1,862.697..2,326.460 rows=32,522 loops=1)

  • Recheck Cond: (status <> 2)
  • Filter: "overlaps"(registration_start_date, registration_end_date, '2020-09-08 09:10:00'::timestamp without time zone, '2020-09-08 09:40:00'::timestamp without time zone)
  • Rows Removed by Filter: 3,147,810
  • Heap Blocks: exact=118,943
8. 1,114.728 1,114.728 ↓ 1.1 3,457,963 1

Bitmap Index Scan on zoom_pool_user_registration_registration_idx (cost=0.00..130,310.97 rows=3,177,407 width=0) (actual time=1,114.728..1,114.728 rows=3,457,963 loops=1)

  • Index Cond: (status <> 2)
9.          

CTE pool_user

10. 0.644 3,730.332 ↑ 1.0 1 1

Limit (cost=13,139.03..13,140.55 rows=1 width=23) (actual time=3,730.323..3,730.332 rows=1 loops=1)

11.          

Initplan (for Limit)

12. 3.031 26.558 ↑ 1.0 1 1

Aggregate (cost=2,720.48..2,720.49 rows=1 width=8) (actual time=26.557..26.558 rows=1 loops=1)

13. 23.527 23.527 ↑ 1.1 46,922 1

Index Only Scan using zoom_pool_user_status_idx on zoom_pool_user (cost=0.42..2,588.62 rows=52,745 width=0) (actual time=0.082..23.527 rows=46,922 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 50,084
  • Heap Fetches: 13,077
14. 1.856 2,371.367 ↑ 1.0 1 1

Aggregate (cost=1,054.55..1,054.56 rows=1 width=8) (actual time=2,371.366..2,371.367 rows=1 loops=1)

15. 2,369.511 2,369.511 ↑ 1.6 29,477 1

CTE Scan on notavailableusers (cost=0.00..937.38 rows=46,869 width=0) (actual time=2,350.646..2,369.511 rows=29,477 loops=1)

16. 0.006 1,152.798 ↑ 1.0 1 1

Aggregate (cost=351.52..351.53 rows=1 width=8) (actual time=1,152.796..1,152.798 rows=1 loops=1)

17. 1,152.792 1,152.792 ↓ 0.0 0 1

CTE Scan on overloadedusers (cost=0.00..312.46 rows=15,623 width=0) (actual time=1,152.791..1,152.792 rows=0 loops=1)

18. 3.324 178.965 ↑ 3.3 7,801 1

Hash Anti Join (cost=5,081.72..44,388.92 rows=25,820 width=23) (actual time=77.248..178.965 rows=7,801 loops=1)

  • Hash Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
19. 12.675 175.636 ↑ 3.9 7,801 1

Merge Anti Join (cost=4,573.98..35,136.97 rows=30,126 width=23) (actual time=77.188..175.636 rows=7,801 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
20. 82.079 82.079 ↑ 2.5 20,794 1

Index Scan using zoom_user_id_uq on zoom_pool_user zpu (cost=0.42..29,971.01 rows=52,745 width=23) (actual time=0.064..82.079 rows=20,794 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 22,244
21. 77.835 80.882 ↑ 3.6 13,125 1

Sort (cost=4,573.56..4,690.73 rows=46,869 width=82) (actual time=76.903..80.882 rows=13,125 loops=1)

  • Sort Key: na.zoom_user_fk
  • Sort Method: quicksort Memory: 3,071kB
22. 3.047 3.047 ↑ 1.6 29,477 1

CTE Scan on notavailableusers na (cost=0.00..937.38 rows=46,869 width=82) (actual time=0.004..3.047 rows=29,477 loops=1)

23. 0.002 0.005 ↓ 0.0 0 1

Hash (cost=312.46..312.46 rows=15,623 width=82) (actual time=0.004..0.005 rows=0 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 128kB
24. 0.003 0.003 ↓ 0.0 0 1

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

25.          

CTE real_pool

26. 0.000 3,730.450 ↑ 1.0 1 1

LockRows (cost=0.44..4.47 rows=1 width=29) (actual time=3,730.447..3,730.450 rows=1 loops=1)

27.          

Initplan (for LockRows)

28. 3,730.330 3,730.330 ↑ 1.0 1 1

CTE Scan on pool_user (cost=0.00..0.02 rows=1 width=82) (actual time=3,730.328..3,730.330 rows=1 loops=1)

29. 3,730.375 3,730.375 ↑ 1.0 1 1

Index Scan using zoom_user_id_uq on zoom_pool_user p (cost=0.42..4.44 rows=1 width=29) (actual time=3,730.374..3,730.375 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = ($6)::text)
  • Filter: (status <> 1)
30. 0.010 3,730.491 ↑ 1.0 1 1

Nested Loop (cost=0.42..4.46 rows=1 width=482) (actual time=3,730.487..3,730.491 rows=1 loops=1)

31. 3,730.465 3,730.465 ↑ 1.0 1 1

CTE Scan on real_pool (cost=0.00..0.02 rows=1 width=188) (actual time=3,730.462..3,730.465 rows=1 loops=1)

32. 0.016 0.016 ↑ 1.0 1 1

Index Scan using zoom_user_id_uq on zoom_pool_user uzpu (cost=0.42..4.44 rows=1 width=372) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
  • Filter: (status <> 1)
Planning time : 2.166 ms
Execution time : 3,737.180 ms