explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xsC6

Settings
# exclusive inclusive rows x rows loops node
1. 353.000 353.000 ↑ 1.6 128 1

CTE Scan on candidates (cost=120,984,024.03..120,984,028.03 rows=200 width=278) (actual time=352.530..353.000 rows=128 loops=1)

2.          

CTE dates_in_interest

3. 0.053 0.054 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.010..0.054 rows=31 loops=1)

4. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

5.          

CTE buckets

6. 0.176 0.176 ↑ 32.3 31 1

CTE Scan on dates_in_interest (cost=0.00..70.00 rows=1,000 width=128) (actual time=0.017..0.176 rows=31 loops=1)

7.          

CTE candidates_r2

8. 90.150 329.120 ↑ 56,229.0 503 1

GroupAggregate (cost=85,621,206.57..93,725,528.23 rows=28,283,200 width=151) (actual time=219.301..329.120 rows=503 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid, buckets.*
  • Filter: (count(*) >= 3)
  • Rows Removed by Filter: 16597
9. 194.804 238.970 ↑ 22,845.1 20,555 1

Sort (cost=85,621,206.57..86,795,156.84 rows=469,580,111 width=139) (actual time=219.260..238.970 rows=20,555 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid, buckets.*
  • Sort Method: quicksort Memory: 6228kB
10. 5.239 44.166 ↑ 22,845.1 20,555 1

Nested Loop (cost=0.43..14,133,689.70 rows=469,580,111 width=139) (actual time=0.069..44.166 rows=20,555 loops=1)

11. 0.239 0.239 ↑ 32.3 31 1

CTE Scan on buckets (cost=0.00..20.00 rows=1,000 width=152) (actual time=0.029..0.239 rows=31 loops=1)

12. 38.688 38.688 ↑ 708.3 663 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord (cost=0.43..9,437.87 rows=469,580 width=51) (actual time=0.024..1.248 rows=663 loops=31)

  • Index Cond: (((opentime)::text >= buckets.r2_from) AND ((opentime)::text < buckets.r2_to))
  • Filter: (userid IS NOT NULL)
  • Rows Removed by Filter: 3
13.          

CTE candidates

14. 0.363 352.926 ↑ 1.6 128 1

Unique (cost=27,117,004.76..27,258,420.76 rows=200 width=484) (actual time=352.527..352.926 rows=128 loops=1)

15. 0.632 352.563 ↑ 94,592.6 299 1

Sort (cost=27,117,004.76..27,187,712.76 rows=28,283,200 width=484) (actual time=352.526..352.563 rows=299 loops=1)

  • Sort Key: candidates_r2.*
  • Sort Method: quicksort Memory: 179kB
16. 1.026 351.931 ↑ 94,592.6 299 1

Nested Loop (cost=0.56..23,262,932.00 rows=28,283,200 width=484) (actual time=219.454..351.931 rows=299 loops=1)

17. 345.875 345.875 ↑ 56,229.0 503 1

CTE Scan on candidates_r2 (cost=0.00..565,664.00 rows=28,283,200 width=464) (actual time=219.309..345.875 rows=503 loops=1)

18. 5.030 5.030 ↑ 1.0 1 503

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.56..0.78 rows=1 width=266) (actual time=0.010..0.010 rows=1 loops=503)

  • Index Cond: ((lockid = (candidates_r2.lockid)::text) AND (userid = (candidates_r2.userid)::text) AND (opentime >= (candidates_r2.buckets).r1_from) AND (opentime < (candidates_r2.buckets).r1_to))
  • Heap Fetches: 4
Planning time : 0.497 ms
Execution time : 353.136 ms