explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZrTY

Settings
# exclusive inclusive rows x rows loops node
1. 0.306 25,491.937 ↑ 1.0 1 1

Update on zoom_pool_user uzpu (cost=364,157.63..364,159.67 rows=1 width=482) (actual time=25,491.915..25,491.937 rows=1 loops=1)

2.          

CTE overloadedusers

3. 4,466.917 7,877.577 ↓ 0.0 0 1

HashAggregate (cost=149,006.18..149,592.04 rows=15,623 width=31) (actual time=7,877.575..7,877.577 rows=0 loops=1)

  • Group Key: zoom_pool_user_registration.zoom_user_fk
  • Filter: (count(*) >= 100)
  • Rows Removed by Filter: 47,499
4. 2,201.965 3,410.660 ↑ 1.0 618,512 1

Bitmap Heap Scan on zoom_pool_user_registration (cost=14,043.80..144,243.56 rows=635,015 width=23) (actual time=1,253.348..3,410.660 rows=618,512 loops=1)

  • Recheck Cond: (create_date > (now() - '1 day'::interval))
  • Heap Blocks: exact=21,578
5. 1,208.695 1,208.695 ↑ 1.0 618,760 1

Bitmap Index Scan on zoom_pool_user_registration_create_date_idx (cost=0.00..13,885.05 rows=635,015 width=0) (actual time=1,208.694..1,208.695 rows=618,760 loops=1)

  • Index Cond: (create_date > (now() - '1 day'::interval))
6.          

CTE notavailableusers

7. 390.287 13,732.856 ↑ 1.6 29,446 1

HashAggregate (cost=174,651.40..175,120.09 rows=46,869 width=23) (actual time=13,603.870..13,732.856 rows=29,446 loops=1)

  • Group Key: zoom_pool_user_registration_1.zoom_user_fk
8. 13,342.569 13,342.569 ↑ 32.6 32,481 1

Seq Scan on zoom_pool_user_registration zoom_pool_user_registration_1 (cost=0.00..172,004.92 rows=1,058,593 width=23) (actual time=8,084.508..13,342.569 rows=32,481 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,491,574
9.          

CTE pool_user

10. 0.640 25,491.443 ↑ 1.0 1 1

Limit (cost=39,438.19..39,438.61 rows=1 width=23) (actual time=25,491.432..25,491.443 rows=1 loops=1)

11.          

Initplan (for Limit)

12. 2.965 688.652 ↑ 1.0 1 1

Aggregate (cost=14,289.61..14,289.62 rows=1 width=8) (actual time=688.651..688.652 rows=1 loops=1)

13. 685.687 685.687 ↓ 1.0 46,978 1

Seq Scan on zoom_pool_user (cost=0.00..14,172.58 rows=46,815 width=0) (actual time=0.017..685.687 rows=46,978 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 50,028
14. 1.844 13,825.259 ↑ 1.0 1 1

Aggregate (cost=1,054.55..1,054.56 rows=1 width=8) (actual time=13,825.258..13,825.259 rows=1 loops=1)

15. 13,823.415 13,823.415 ↑ 1.6 29,446 1

CTE Scan on notavailableusers (cost=0.00..937.38 rows=46,869 width=0) (actual time=13,603.877..13,823.415 rows=29,446 loops=1)

16. 0.004 7,877.583 ↑ 1.0 1 1

Aggregate (cost=351.52..351.53 rows=1 width=8) (actual time=7,877.582..7,877.583 rows=1 loops=1)

17. 7,877.579 7,877.579 ↓ 0.0 0 1

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

18. 35.496 3,099.309 ↑ 1.9 10,913 1

Hash Anti Join (cost=22,885.48..31,455.00 rows=20,299 width=23) (actual time=2,758.566..3,099.309 rows=10,913 loops=1)

  • Hash Cond: ((zpu.zoom_user_id)::text = (ov.zoom_user_fk)::text)
19. 154.259 3,063.807 ↑ 2.2 10,913 1

Merge Anti Join (cost=22,377.73..23,072.34 rows=24,196 width=23) (actual time=2,758.488..3,063.807 rows=10,913 loops=1)

  • Merge Cond: ((zpu.zoom_user_id)::text = (na.zoom_user_fk)::text)
20. 1,570.943 2,097.417 ↑ 1.6 28,892 1

Sort (cost=17,804.17..17,921.21 rows=46,815 width=23) (actual time=1,987.951..2,097.417 rows=28,892 loops=1)

  • Sort Key: zpu.zoom_user_id
  • Sort Method: quicksort Memory: 5,207kB
21. 526.474 526.474 ↓ 1.0 46,978 1

Seq Scan on zoom_pool_user zpu (cost=0.00..14,172.58 rows=46,815 width=23) (actual time=0.027..526.474 rows=46,978 loops=1)

  • Filter: (status <> 1)
  • Rows Removed by Filter: 50,028
22. 763.717 812.131 ↑ 2.6 18,146 1

Sort (cost=4,573.56..4,690.73 rows=46,869 width=82) (actual time=770.509..812.131 rows=18,146 loops=1)

  • Sort Key: na.zoom_user_fk
  • Sort Method: quicksort Memory: 3,069kB
23. 48.414 48.414 ↑ 1.6 29,446 1

CTE Scan on notavailableusers na (cost=0.00..937.38 rows=46,869 width=82) (actual time=0.003..48.414 rows=29,446 loops=1)

24. 0.002 0.006 ↓ 0.0 0 1

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

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

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

26.          

CTE real_pool

27. 0.000 25,491.574 ↑ 1.0 1 1

LockRows (cost=2.45..4.47 rows=1 width=29) (actual time=25,491.571..25,491.574 rows=1 loops=1)

28.          

Initplan (for LockRows)

29. 25,491.438 25,491.438 ↑ 1.0 1 1

CTE Scan on pool_user (cost=0.00..0.02 rows=1 width=82) (actual time=25,491.436..25,491.438 rows=1 loops=1)

30. 0.030 25,491.520 ↑ 1.0 1 1

Bitmap Heap Scan on zoom_pool_user p (cost=2.43..4.44 rows=1 width=29) (actual time=25,491.518..25,491.520 rows=1 loops=1)

  • Recheck Cond: ((zoom_user_id)::text = ($6)::text)
  • Filter: (status <> 1)
  • Heap Blocks: exact=1
31. 25,491.490 25,491.490 ↑ 1.0 1 1

Bitmap Index Scan on zoom_user_id_uq (cost=0.00..2.42 rows=1 width=0) (actual time=25,491.490..25,491.490 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = ($6)::text)
32. 0.018 25,491.631 ↑ 1.0 1 1

Nested Loop (cost=2.43..4.46 rows=1 width=482) (actual time=25,491.626..25,491.631 rows=1 loops=1)

33. 25,491.595 25,491.595 ↑ 1.0 1 1

CTE Scan on real_pool (cost=0.00..0.02 rows=1 width=188) (actual time=25,491.592..25,491.595 rows=1 loops=1)

34. 0.008 0.018 ↑ 1.0 1 1

Bitmap Heap Scan on zoom_pool_user uzpu (cost=2.43..4.44 rows=1 width=372) (actual time=0.017..0.018 rows=1 loops=1)

  • Recheck Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
  • Filter: (status <> 1)
  • Heap Blocks: exact=1
35. 0.010 0.010 ↑ 1.0 1 1

Bitmap Index Scan on zoom_user_id_uq (cost=0.00..2.42 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((zoom_user_id)::text = (real_pool.zoom_user_id)::text)
Planning time : 1.906 ms
Execution time : 25,498.472 ms