explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ongf

Settings
# exclusive inclusive rows x rows loops node
1. 0.359 2,483.158 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=271,142.80..271,146.84 rows=1 width=481) (actual time=2,483.119..2,483.158 rows=1 loops=1)

2.          

CTE overloadedusers

3. 460.852 1,081.822 ↓ 0.0 0 1

HashAggregate (cost=72,305.41..72,856.48 rows=14,695 width=31) (actual time=1,081.821..1,081.822 rows=0 loops=1)

  • Group Key: zoom_pool_user_registration.zoom_user_fk
  • Filter: (count(*) >= 100)
  • Rows Removed by Filter: 36,889
4. 620.970 620.970 ↓ 1.1 515,795 1

Index Only Scan using zoom_pool_user_registration_create_date_idx on zoom_pool_user_registration (cost=0.43..68,737.06 rows=475,780 width=23) (actual time=0.299..620.970 rows=515,795 loops=1)

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

CTE notavailableusers

6. 27.214 1,196.178 ↑ 2.2 20,338 1

HashAggregate (cost=182,125.01..182,565.87 rows=44,086 width=23) (actual time=1,187.223..1,196.178 rows=20,338 loops=1)

  • Group Key: zoom_pool_user_registration_1.zoom_user_fk
7. 1,168.964 1,168.964 ↑ 45.1 20,339 1

Seq Scan on zoom_pool_user_registration zoom_pool_user_registration_1 (cost=0.00..179,832.35 rows=917,065 width=23) (actual time=0.087..1,168.964 rows=20,339 loops=1)

  • Filter: ((status <> 2) AND "overlaps"(registration_start_date, registration_end_date, '2020-09-11 15:20:00'::timestamp without time zone, '2020-09-11 15:50:00'::timestamp without time zone))
  • Rows Removed by Filter: 2,989,614
8.          

CTE pool_user

9. 0.562 2,482.137 ↑ 1.0 1 1

Limit (cost=15,713.78..15,715.57 rows=1 width=23) (actual time=2,482.122..2,482.137 rows=1 loops=1)

10.          

Initplan (for Limit)

11. 2.867 61.534 ↑ 1.0 1 1

Aggregate (cost=5,756.62..5,756.63 rows=1 width=8) (actual time=61.530..61.534 rows=1 loops=1)

12. 58.667 58.667 ↓ 1.0 36,882 1

Index Only Scan using zoom_pool_user_status_idx on zoom_pool_user (cost=0.42..5,664.82 rows=36,720 width=0) (actual time=0.172..58.667 rows=36,882 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 60,124
  • Heap Fetches: 44,242
13. 1.340 1,204.738 ↑ 1.0 1 1

Aggregate (cost=991.94..991.95 rows=1 width=8) (actual time=1,204.733..1,204.738 rows=1 loops=1)

14. 1,203.398 1,203.398 ↑ 2.2 20,338 1

CTE Scan on notavailableusers (cost=0.00..881.72 rows=44,086 width=0) (actual time=1,187.240..1,203.398 rows=20,338 loops=1)

15. 0.014 1,081.850 ↑ 1.0 1 1

Aggregate (cost=330.64..330.65 rows=1 width=8) (actual time=1,081.849..1,081.850 rows=1 loops=1)

16. 1,081.836 1,081.836 ↓ 0.0 0 1

CTE Scan on overloadedusers (cost=0.00..293.90 rows=14,695 width=0) (actual time=1,081.836..1,081.836 rows=0 loops=1)

17. 0.977 133.453 ↑ 2.8 6,154 1

Merge Anti Join (cost=5,593.96..35,994.62 rows=16,997 width=23) (actual time=50.243..133.453 rows=6,154 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
18. 7.921 132.454 ↑ 3.3 6,154 1

Merge Anti Join (cost=4,282.94..34,529.70 rows=20,032 width=23) (actual time=50.211..132.454 rows=6,154 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
19. 72.048 72.048 ↑ 2.7 13,717 1

Index Scan using zoom_user_id_uq on zoom_pool_user zpu (cost=0.42..29,768.07 rows=36,720 width=23) (actual time=0.084..72.048 rows=13,717 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 23,588
20. 49.481 52.485 ↑ 5.8 7,566 1

Sort (cost=4,282.52..4,392.74 rows=44,086 width=82) (actual time=50.099..52.485 rows=7,566 loops=1)

  • Sort Key: na.zoom_user_fk
  • Sort Method: quicksort Memory: 2,357kB
21. 3.004 3.004 ↑ 2.2 20,338 1

CTE Scan on notavailableusers na (cost=0.00..881.72 rows=44,086 width=82) (actual time=0.013..3.004 rows=20,338 loops=1)

22. 0.017 0.022 ↓ 0.0 0 1

Sort (cost=1,311.02..1,347.75 rows=14,695 width=82) (actual time=0.021..0.022 rows=0 loops=1)

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

CTE Scan on overloadedusers ov (cost=0.00..293.90 rows=14,695 width=82) (actual time=0.005..0.005 rows=0 loops=1)

24.          

CTE real_pool

25. 0.000 2,482.681 ↑ 1.0 1 1

LockRows (cost=0.44..4.47 rows=1 width=29) (actual time=2,482.677..2,482.681 rows=1 loops=1)

26.          

Initplan (for LockRows)

27. 2,482.138 2,482.138 ↑ 1.0 1 1

CTE Scan on pool_user (cost=0.00..0.02 rows=1 width=82) (actual time=2,482.134..2,482.138 rows=1 loops=1)

28. 2,482.200 2,482.200 ↑ 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=2,482.198..2,482.200 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = ($6)::text)
  • Filter: (status <> 1)
29. 0.022 2,482.799 ↑ 1.0 1 1

Nested Loop (cost=0.42..4.46 rows=1 width=481) (actual time=2,482.791..2,482.799 rows=1 loops=1)

30. 2,482.755 2,482.755 ↑ 1.0 1 1

CTE Scan on real_pool (cost=0.00..0.02 rows=1 width=188) (actual time=2,482.749..2,482.755 rows=1 loops=1)

31. 0.022 0.022 ↑ 1.0 1 1

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

  • Index Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
  • Filter: (status <> 1)
Planning time : 3.343 ms
Execution time : 2,488.663 ms