explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mvGZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.291 1,303.558 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=158,965.78..172,738.22 rows=1 width=598) (actual time=1,252.888..1,303.558 rows=1 loops=1)

2.          

CTE overloadedusers

3. 295.403 833.188 ↓ 0.0 0 1

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

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

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

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

CTE notavailableusers

6. 0.353 257.829 ↓ 0.0 0 1

HashAggregate (cost=59,733.48..60,200.79 rows=46,731 width=23) (actual time=257.826..257.829 rows=0 loops=1)

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

Hash Join (cost=13,709.56..59,616.65 rows=46,731 width=23) (actual time=257.473..257.476 rows=0 loops=1)

  • Hash Cond: ((r.zoom_user_fk)::text = (u.zoom_user_id)::text)
8. 257.471 257.471 ↓ 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=257.470..257.471 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: 815,440
9. 0.000 0.000 ↓ 0.0 0

Hash (cost=13,585.00..13,585.00 rows=9,965 width=23) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on zoom_pool_user u (cost=0.00..13,585.00 rows=9,965 width=23) (never executed)

  • Filter: (pool_group_level = 5)
11.          

CTE pool_user

12. 0.458 1,211.166 ↑ 1.0 1 1

Limit (cost=33,856.18..33,860.32 rows=1 width=23) (actual time=1,211.156..1,211.166 rows=1 loops=1)

13.          

Initplan (for Limit)

14. 0.772 49.124 ↑ 1.0 1 1

Aggregate (cost=13,734.90..13,734.91 rows=1 width=8) (actual time=49.123..49.124 rows=1 loops=1)

15. 48.352 48.352 ↓ 1.0 10,014 1

Seq Scan on zoom_pool_user (cost=0.00..13,710.00 rows=9,961 width=0) (actual time=0.006..48.352 rows=10,014 loops=1)

  • Filter: ((status <> 1) AND (pool_group_level = 5))
  • Rows Removed by Filter: 39,986
16. 0.006 257.837 ↑ 1.0 1 1

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

17. 257.831 257.831 ↓ 0.0 0 1

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

18. 0.007 833.199 ↑ 1.0 1 1

Aggregate (cost=354.80..354.81 rows=1 width=8) (actual time=833.198..833.199 rows=1 loops=1)

19. 833.192 833.192 ↓ 0.0 0 1

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

20. 2.015 70.548 ↓ 2.2 7,539 1

Hash Anti Join (cost=17,305.13..31,399.73 rows=3,409 width=23) (actual time=66.645..70.548 rows=7,539 loops=1)

  • Hash Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
21. 1.054 68.530 ↓ 1.1 7,539 1

Merge Anti Join (cost=15,786.37..15,946.44 rows=6,819 width=23) (actual time=66.354..68.530 rows=7,539 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
22. 25.073 67.452 ↑ 1.3 7,539 1

Sort (cost=14,371.51..14,396.42 rows=9,961 width=23) (actual time=66.321..67.452 rows=7,539 loops=1)

  • Sort Key: zpu.zoom_user_id
  • Sort Method: quicksort Memory: 1,167kB
23. 42.379 42.379 ↓ 1.0 10,014 1

Seq Scan on zoom_pool_user zpu (cost=0.00..13,710.00 rows=9,961 width=23) (actual time=0.039..42.379 rows=10,014 loops=1)

  • Filter: ((status <> 1) AND (pool_group_level = 5))
  • Rows Removed by Filter: 39,986
24. 0.021 0.024 ↓ 0.0 0 1

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

  • Sort Key: ov.zoom_user_fk
  • Sort Method: quicksort Memory: 25kB
25. 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)

26. 0.001 0.003 ↓ 0.0 0 1

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

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

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

28.          

CTE real_pool

29. 0.000 1,251.343 ↑ 1.0 1 1

LockRows (cost=0.02..13,710.03 rows=1 width=29) (actual time=1,212.183..1,251.343 rows=1 loops=1)

30.          

Initplan (for LockRows)

31. 1,211.164 1,211.164 ↑ 1.0 1 1

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

32. 1,251.252 1,251.252 ↑ 1.0 1 1

Seq Scan on zoom_pool_user p (cost=0.00..13,710.00 rows=1 width=29) (actual time=1,212.096..1,251.252 rows=1 loops=1)

  • Filter: ((status <> 1) AND ((zoom_user_id)::text = ($6)::text))
  • Rows Removed by Filter: 49,999
33. 7.294 1,303.267 ↑ 1.0 1 1

Hash Join (cost=0.03..13,772.47 rows=1 width=598) (actual time=1,252.622..1,303.267 rows=1 loops=1)

  • Hash Cond: ((uzpu.zoom_user_id)::text = (real_pool.zoom_user_id)::text)
34. 44.604 44.604 ↓ 1.0 49,983 1

Seq Scan on zoom_pool_user uzpu (cost=0.00..13,585.00 rows=49,980 width=480) (actual time=0.017..44.604 rows=49,983 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 17
35. 0.009 1,251.369 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=188) (actual time=1,251.368..1,251.369 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 1,251.360 1,251.360 ↑ 1.0 1 1

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

Planning time : 1.051 ms
Execution time : 1,304.908 ms