explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WE6Z

Settings
# exclusive inclusive rows x rows loops node
1. 770.915 4,941.922 ↑ 42.2 349 1

Merge Join (cost=95,679,973.41..96,059,196.96 rows=14,738 width=291) (actual time=83.863..4,941.922 rows=349 loops=1)

  • Merge Cond: (((opendoorrecord.lockid)::text = (candidates_r2.lockid)::text) AND ((opendoorrecord.userid)::text = (candidates_r2.userid)::text))
  • Join Filter: (((opendoorrecord.opentime)::text >= (candidates_r2.buckets).r1_from) AND ((opendoorrecord.opentime)::text < (candidates_r2.buckets).r1_to))
  • Rows Removed by Join Filter: 85656
2.          

CTE dates_in_interest

3. 0.033 0.034 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.010..0.034 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.106 0.106 ↑ 32.3 31 1

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

7.          

CTE candidates_r2

8. 14.615 65.802 ↑ 57,990.7 485 1

GroupAggregate (cost=84,596,104.78..91,489,194.51 rows=28,125,467 width=119) (actual time=48.209..65.802 rows=485 loops=1)

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

Sort (cost=84,596,104.78..85,763,781.73 rows=467,070,778 width=119) (actual time=48.180..51.187 rows=20,398 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6187kB
10. 2.857 11.647 ↑ 22,897.9 20,398 1

Nested Loop (cost=0.43..14,056,002.50 rows=467,070,778 width=119) (actual time=0.047..11.647 rows=20,398 loops=1)

11. 0.141 0.141 ↑ 32.3 31 1

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

12. 8.649 8.649 ↑ 709.8 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..9,385.27 rows=467,071 width=51) (actual time=0.017..0.279 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. 4,097.263 4,097.263 ↑ 1.0 4,203,294 1

Index Scan using opendoorrecord_lockid_userid_opentime_openmode_idx on opendoorrecord (cost=0.56..74,885.73 rows=4,218,825 width=103) (actual time=0.013..4,097.263 rows=4,203,294 loops=1)

14. 7.244 73.744 ↑ 327.2 85,951 1

Materialize (cost=4,190,703.30..4,331,330.63 rows=28,125,467 width=188) (actual time=66.187..73.744 rows=85,951 loops=1)

15. 0.476 66.500 ↑ 57,990.7 485 1

Sort (cost=4,190,703.30..4,261,016.97 rows=28,125,467 width=188) (actual time=66.185..66.500 rows=485 loops=1)

  • Sort Key: candidates_r2.lockid, candidates_r2.userid
  • Sort Method: quicksort Memory: 153kB
16. 66.024 66.024 ↑ 57,990.7 485 1

CTE Scan on candidates_r2 (cost=0.00..562,509.34 rows=28,125,467 width=188) (actual time=48.212..66.024 rows=485 loops=1)

Planning time : 0.418 ms
Execution time : 4,942.084 ms