explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MG6M

Settings
# exclusive inclusive rows x rows loops node
1. 1,558.261 6,865.697 ↑ 7.5 5,872 1

Merge Join (cost=108,426,776.42..109,371,826.20 rows=44,151 width=445) (actual time=101.489..6,865.697 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.r1_from) AND ((opendoorrecord.opentime)::text < candidates.r1_to))
  • Rows Removed by Join Filter: 2145667
2.          

CTE dates_in_interest

3. 0.060 0.061 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.022..0.061 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.156 0.156 ↑ 32.3 31 1

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

7.          

CTE candidates

8. 15.563 83.381 ↑ 4,946.5 17,046 1

Unique (cost=89,457,777.79..94,125,207.79 rows=84,317,200 width=331) (actual time=64.145..83.381 rows=17,046 loops=1)

9. 52.818 67.818 ↑ 22,887.4 20,393 1

Sort (cost=89,457,777.79..90,624,635.29 rows=466,743,000 width=331) (actual time=64.143..67.818 rows=20,393 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6512kB
10. 4.311 15.000 ↑ 22,887.4 20,393 1

Nested Loop (cost=0.43..14,046,862.40 rows=466,743,000 width=331) (actual time=0.078..15.000 rows=20,393 loops=1)

11. 0.211 0.211 ↑ 32.3 31 1

CTE Scan on buckets (cost=0.00..20.00 rows=1,000 width=280) (actual time=0.042..0.211 rows=31 loops=1)

12. 10.478 10.478 ↑ 709.3 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..9,379.41 rows=466,743 width=51) (actual time=0.020..0.338 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. 5,031.585 5,031.585 ↑ 1.0 4,210,536 1

Index Scan using opendoorrecord_lockid_userid_opentime_openmode_idx on opendoorrecord (cost=0.56..74,838.62 rows=4,215,864 width=103) (actual time=0.013..5,031.585 rows=4,210,536 loops=1)

14. 172.241 275.851 ↑ 39.2 2,151,434 1

Materialize (cost=14,301,493.03..14,723,079.03 rows=84,317,200 width=342) (actual time=100.548..275.851 rows=2,151,434 loops=1)

15. 12.118 103.610 ↑ 4,946.5 17,046 1

Sort (cost=14,301,493.03..14,512,286.03 rows=84,317,200 width=342) (actual time=100.546..103.610 rows=17,046 loops=1)

  • Sort Key: candidates.lockid, candidates.userid
  • Sort Method: quicksort Memory: 5296kB
16. 91.492 91.492 ↑ 4,946.5 17,046 1

CTE Scan on candidates (cost=0.00..1,686,344.00 rows=84,317,200 width=342) (actual time=64.149..91.492 rows=17,046 loops=1)

Planning time : 0.411 ms
Execution time : 6,867.696 ms