explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uixK

Settings
# exclusive inclusive rows x rows loops node
1. 3.880 4,374.934 ↑ 12.0 3,667 1

Unique (cost=104,632,789.03..104,633,230.82 rows=44,179 width=266) (actual time=4,370.606..4,374.934 rows=3,667 loops=1)

2.          

CTE dates_in_interest

3. 0.046 0.047 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.011..0.047 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.139 0.139 ↑ 32.3 31 1

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

7.          

CTE candidates

8. 15.702 80.624 ↑ 4,948.0 17,046 1

Unique (cost=85,109,961.08..89,778,863.30 rows=84,343,800 width=139) (actual time=60.536..80.624 rows=17,046 loops=1)

9. 50.062 64.922 ↑ 22,894.6 20,393 1

Sort (cost=85,109,961.08..86,277,186.63 rows=466,890,222 width=139) (actual time=60.534..64.922 rows=20,393 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6185kB
10. 3.982 14.860 ↑ 22,894.6 20,393 1

Nested Loop (cost=0.43..14,051,292.60 rows=466,890,222 width=139) (actual time=0.057..14.860 rows=20,393 loops=1)

11. 0.183 0.183 ↑ 32.3 31 1

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

12. 10.695 10.695 ↑ 709.6 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..9,382.37 rows=466,890 width=51) (actual time=0.020..0.345 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. 13.718 4,371.054 ↑ 7.5 5,872 1

Sort (cost=14,853,850.69..14,853,961.13 rows=44,179 width=266) (actual time=4,370.604..4,371.054 rows=5,872 loops=1)

  • Sort Key: candidates.lockid, candidates.userid, candidates.buckets
  • Sort Method: quicksort Memory: 1752kB
14. 1,494.132 4,357.336 ↑ 7.5 5,872 1

Merge Join (cost=13,910,835.70..14,850,442.04 rows=44,179 width=266) (actual time=94.409..4,357.336 rows=5,872 loops=1)

  • Merge Cond: (((opendoorrecord.lockid)::text = (candidates.lockid)::text) AND ((opendoorrecord.userid)::text = (candidates.userid)::text))
  • Join Filter: (((opendoorrecord.opentime)::text >= (candidates.buckets).r1_from) AND ((opendoorrecord.opentime)::text < (candidates.buckets).r1_to))
  • Rows Removed by Join Filter: 2145809
15. 2,587.095 2,587.095 ↑ 1.0 4,211,027 1

Index Only Scan using opendoorrecord_lockid_userid_opentime_openmode_idx on opendoorrecord (cost=0.56..69,118.77 rows=4,217,194 width=51) (actual time=0.027..2,587.095 rows=4,211,027 loops=1)

  • Heap Fetches: 1048656
16. 179.364 276.109 ↑ 39.2 2,151,576 1

Materialize (cost=13,910,835.14..14,332,554.14 rows=84,343,800 width=246) (actual time=93.844..276.109 rows=2,151,576 loops=1)

17. 9.585 96.745 ↑ 4,948.0 17,046 1

Sort (cost=13,910,835.14..14,121,694.64 rows=84,343,800 width=246) (actual time=93.841..96.745 rows=17,046 loops=1)

  • Sort Key: candidates.lockid, candidates.userid
  • Sort Method: quicksort Memory: 5296kB
18. 87.160 87.160 ↑ 4,948.0 17,046 1

CTE Scan on candidates (cost=0.00..1,686,876.00 rows=84,343,800 width=246) (actual time=60.538..87.160 rows=17,046 loops=1)

Planning time : 0.578 ms
Execution time : 4,376.288 ms