explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eUa2

Settings
# exclusive inclusive rows x rows loops node
1. 0.811 138.567 ↑ 80,657.7 349 1

Nested Loop (cost=91,550,503.79..114,703,494.12 rows=28,149,533 width=291) (actual time=54.711..138.567 rows=349 loops=1)

2.          

CTE dates_in_interest

3. 0.042 0.043 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.010..0.043 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.000..0.001 rows=1 loops=1)

5.          

CTE buckets

6. 0.127 0.127 ↑ 32.3 31 1

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

7.          

CTE candidates_r2

8. 17.153 75.741 ↑ 58,040.3 485 1

GroupAggregate (cost=84,652,817.91..91,550,428.19 rows=28,149,533 width=119) (actual time=53.977..75.741 rows=485 loops=1)

  • Group Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Filter: (count(*) >= 3)
  • Rows Removed by Filter: 16562
9. 45.751 58.588 ↑ 22,912.1 20,398 1

Sort (cost=84,652,817.91..85,821,218.47 rows=467,360,222 width=119) (actual time=53.947..58.588 rows=20,398 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6187kB
10. 3.215 12.837 ↑ 22,912.1 20,398 1

Nested Loop (cost=0.43..14,066,913.30 rows=467,360,222 width=119) (actual time=0.048..12.837 rows=20,398 loops=1)

11. 0.167 0.167 ↑ 32.3 31 1

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

12. 9.455 9.455 ↑ 710.3 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..9,393.29 rows=467,360 width=51) (actual time=0.020..0.305 rows=658 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. 76.161 76.161 ↑ 58,040.3 485 1

CTE Scan on candidates_r2 (cost=0.00..562,990.66 rows=28,149,533 width=188) (actual time=53.981..76.161 rows=485 loops=1)

14. 61.595 61.595 ↑ 1.0 1 485

Index Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.56..0.78 rows=1 width=103) (actual time=0.126..0.127 rows=1 loops=485)

  • Index Cond: (((candidates_r2.lockid)::text = (lockid)::text) AND ((candidates_r2.userid)::text = (userid)::text) AND ((opentime)::text >= (candidates_r2.buckets).r1_from) AND ((opentime)::text < (candidates_r2.buckets).r1_to))
Planning time : 0.417 ms
Execution time : 138.715 ms