explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Jjg

Settings
# exclusive inclusive rows x rows loops node
1. 0.756 37,750.199 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=331,785.83..331,789.87 rows=1 width=482) (actual time=37,750.118..37,750.199 rows=1 loops=1)

2.          

CTE overloadedusers

3. 6,952.797 15,216.667 ↓ 0.0 0 1

HashAggregate (cost=140,793.55..141,379.41 rows=15,623 width=31) (actual time=15,216.657..15,216.667 rows=0 loops=1)

  • Group Key: zoom_pool_user_registration.zoom_user_fk
  • Filter: (count(*) >= 100)
  • Rows Removed by Filter: 47,499
4. 8,263.870 8,263.870 ↑ 1.0 612,368 1

Index Only Scan using zoom_pool_user_registration_create_date_idx on zoom_pool_user_registration (cost=0.43..136,047.21 rows=632,845 width=23) (actual time=0.629..8,263.870 rows=612,368 loops=1)

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

CTE notavailableusers

6. 575.446 19,392.469 ↑ 1.6 29,065 1

HashAggregate (cost=174,054.50..174,523.19 rows=46,869 width=23) (actual time=19,296.995..19,392.469 rows=29,065 loops=1)

  • Group Key: zoom_pool_user_registration_1.zoom_user_fk
7. 18,817.023 18,817.023 ↑ 33.0 31,995 1

Seq Scan on zoom_pool_user_registration zoom_pool_user_registration_1 (cost=0.00..171,417.06 rows=1,054,975 width=23) (actual time=12,125.702..18,817.023 rows=31,995 loops=1)

  • Filter: ((status <> 2) AND "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,480,611
8.          

CTE pool_user

9. 32.004 37,748.945 ↑ 1.0 1 1

Limit (cost=15,876.84..15,878.35 rows=1 width=23) (actual time=37,748.913..37,748.945 rows=1 loops=1)

10.          

Initplan (for Limit)

11. 100.153 515.166 ↑ 1.0 1 1

Aggregate (cost=5,445.51..5,445.52 rows=1 width=8) (actual time=515.149..515.166 rows=1 loops=1)

12. 415.013 415.013 ↓ 1.0 46,976 1

Index Only Scan using zoom_pool_user_status_idx on zoom_pool_user (cost=0.42..5,328.69 rows=46,728 width=0) (actual time=0.126..415.013 rows=46,976 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 50,030
  • Heap Fetches: 45,248
13. 1.929 19,528.713 ↑ 1.0 1 1

Aggregate (cost=1,054.55..1,054.56 rows=1 width=8) (actual time=19,528.712..19,528.713 rows=1 loops=1)

14. 19,526.784 19,526.784 ↑ 1.6 29,065 1

CTE Scan on notavailableusers (cost=0.00..937.38 rows=46,869 width=0) (actual time=19,297.043..19,526.784 rows=29,065 loops=1)

15. 0.030 15,216.720 ↑ 1.0 1 1

Aggregate (cost=351.52..351.53 rows=1 width=8) (actual time=15,216.719..15,216.720 rows=1 loops=1)

16. 15,216.690 15,216.690 ↓ 0.0 0 1

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

17. 1.775 2,456.342 ↑ 1.8 10,956 1

Merge Anti Join (cost=5,974.69..36,481.65 rows=20,261 width=23) (actual time=932.788..2,456.342 rows=10,956 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
18. 256.595 2,454.537 ↑ 2.2 10,956 1

Merge Anti Join (cost=4,573.98..34,903.54 rows=24,151 width=23) (actual time=932.669..2,454.537 rows=10,956 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
19. 1,259.833 1,259.833 ↑ 1.6 28,385 1

Index Scan using zoom_user_id_uq on zoom_pool_user zpu (cost=0.42..29,753.05 rows=46,728 width=23) (actual time=0.167..1,259.833 rows=28,385 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 30,344
20. 935.086 938.109 ↑ 2.7 17,597 1

Sort (cost=4,573.56..4,690.73 rows=46,869 width=82) (actual time=932.217..938.109 rows=17,597 loops=1)

  • Sort Key: na.zoom_user_fk
  • Sort Method: quicksort Memory: 3,039kB
21. 3.023 3.023 ↑ 1.6 29,065 1

CTE Scan on notavailableusers na (cost=0.00..937.38 rows=46,869 width=82) (actual time=0.029..3.023 rows=29,065 loops=1)

22. 0.019 0.030 ↓ 0.0 0 1

Sort (cost=1,400.71..1,439.77 rows=15,623 width=82) (actual time=0.029..0.030 rows=0 loops=1)

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

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

24.          

CTE real_pool

25. 0.000 37,749.221 ↑ 1.0 1 1

LockRows (cost=0.44..4.47 rows=1 width=29) (actual time=37,749.217..37,749.221 rows=1 loops=1)

26.          

Initplan (for LockRows)

27. 37,748.963 37,748.963 ↑ 1.0 1 1

CTE Scan on pool_user (cost=0.00..0.02 rows=1 width=82) (actual time=37,748.952..37,748.963 rows=1 loops=1)

28. 37,749.079 37,749.079 ↑ 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=37,749.077..37,749.079 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = ($6)::text)
  • Filter: (status <> 1)
29. 0.073 37,749.443 ↑ 1.0 1 1

Nested Loop (cost=0.42..4.46 rows=1 width=482) (actual time=37,749.419..37,749.443 rows=1 loops=1)

30. 37,749.343 37,749.343 ↑ 1.0 1 1

CTE Scan on real_pool (cost=0.00..0.02 rows=1 width=188) (actual time=37,749.329..37,749.343 rows=1 loops=1)

31. 0.027 0.027 ↑ 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.027..0.027 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
  • Filter: (status <> 1)
Planning time : 3.172 ms
Execution time : 37,756.860 ms