explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8WQZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.800 1,116.199 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=119,170.46..119,174.50 rows=1 width=598) (actual time=1,116.072..1,116.199 rows=1 loops=1)

2.          

CTE overloadedusers

3. 278.767 789.283 ↓ 0.0 0 1

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

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

Seq Scan on zoom_pool_user_registration (cost=0.00..47,209.13 rows=452,551 width=23) (actual time=0.029..510.516 rows=496,785 loops=1)

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

CTE notavailableusers

6. 0.335 242.525 ↓ 0.0 0 1

HashAggregate (cost=55,004.64..55,476.44 rows=47,180 width=23) (actual time=242.465..242.525 rows=0 loops=1)

  • Group Key: r.zoom_user_fk
7. 0.042 242.190 ↓ 0.0 0 1

Hash Join (cost=8,979.00..54,886.09 rows=47,420 width=23) (actual time=242.143..242.190 rows=0 loops=1)

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

Hash (cost=8,852.60..8,852.60 rows=10,112 width=23) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Scan using zoom_pool_user_status_group_level_idx on zoom_pool_user u (cost=0.29..8,852.60 rows=10,112 width=23) (never executed)

  • Index Cond: (pool_group_level = 5)
11.          

CTE pool_user

12. 0.494 1,114.879 ↑ 1.0 1 1

Limit (cost=12,485.99..12,494.53 rows=1 width=23) (actual time=1,114.845..1,114.879 rows=1 loops=1)

13.          

Initplan (for Limit)

14. 0.766 11.970 ↑ 1.0 1 1

Aggregate (cost=2,095.53..2,095.54 rows=1 width=8) (actual time=11.954..11.970 rows=1 loops=1)

15. 11.204 11.204 ↑ 1.0 10,017 1

Index Only Scan using zoom_pool_user_status_group_level_idx on zoom_pool_user (cost=0.29..2,070.26 rows=10,108 width=0) (actual time=2.189..11.204 rows=10,017 loops=1)

  • Index Cond: (pool_group_level = 5)
  • Filter: (status <> 1)
  • Rows Removed by Filter: 3
  • Heap Fetches: 6,740
16. 0.006 242.480 ↑ 1.0 1 1

Aggregate (cost=1,061.55..1,061.56 rows=1 width=8) (actual time=242.473..242.480 rows=1 loops=1)

17. 242.474 242.474 ↓ 0.0 0 1

CTE Scan on notavailableusers (cost=0.00..943.60 rows=47,180 width=0) (actual time=242.467..242.474 rows=0 loops=1)

18. 0.028 789.328 ↑ 1.0 1 1

Aggregate (cost=354.80..354.81 rows=1 width=8) (actual time=789.327..789.328 rows=1 loops=1)

19. 789.300 789.300 ↓ 0.0 0 1

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

20. 0.759 70.607 ↓ 1.7 6,045 1

Merge Anti Join (cost=6,021.43..35,547.90 rows=3,460 width=23) (actual time=59.155..70.607 rows=6,045 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
21. 0.643 69.765 ↑ 1.1 6,045 1

Merge Anti Join (cost=1,415.27..30,623.25 rows=6,920 width=23) (actual time=59.030..69.765 rows=6,045 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
22. 68.955 68.955 ↑ 1.7 6,045 1

Index Scan using zoom_user_id_uq on zoom_pool_user zpu (cost=0.41..29,072.39 rows=10,108 width=23) (actual time=58.820..68.955 rows=6,045 loops=1)

  • Filter: ((status <> 1) AND (pool_group_level = 5))
  • Rows Removed by Filter: 39,982
23. 0.148 0.167 ↓ 0.0 0 1

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

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

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

25. 0.067 0.083 ↓ 0.0 0 1

Sort (cost=4,606.16..4,724.11 rows=47,180 width=82) (actual time=0.082..0.083 rows=0 loops=1)

  • Sort Key: na.zoom_user_fk
  • Sort Method: quicksort Memory: 25kB
26. 0.016 0.016 ↓ 0.0 0 1

CTE Scan on notavailableusers na (cost=0.00..943.60 rows=47,180 width=82) (actual time=0.016..0.016 rows=0 loops=1)

27.          

CTE real_pool

28. 0.000 1,115.148 ↑ 1.0 1 1

LockRows (cost=0.43..4.46 rows=1 width=29) (actual time=1,115.145..1,115.148 rows=1 loops=1)

29.          

Initplan (for LockRows)

30. 1,114.889 1,114.889 ↑ 1.0 1 1

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

31. 1,115.020 1,115.020 ↑ 1.0 1 1

Index Scan using zoom_user_id_uq on zoom_pool_user p (cost=0.41..4.44 rows=1 width=29) (actual time=1,115.017..1,115.020 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = ($6)::text)
  • Filter: (status <> 1)
32. 0.074 1,115.399 ↑ 1.0 1 1

Nested Loop (cost=0.41..4.46 rows=1 width=598) (actual time=1,115.387..1,115.399 rows=1 loops=1)

33. 1,115.289 1,115.289 ↑ 1.0 1 1

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

34. 0.036 0.036 ↑ 1.0 1 1

Index Scan using zoom_user_id_uq on zoom_pool_user uzpu (cost=0.41..4.44 rows=1 width=480) (actual time=0.036..0.036 rows=1 loops=1)

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