explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2khl

Settings
# exclusive inclusive rows x rows loops node
1. 3.735 7,058.921 ↑ 12.0 3,667 1

Unique (cost=104,637,102.53..104,637,544.32 rows=44,179 width=266) (actual time=7,054.777..7,058.921 rows=3,667 loops=1)

2.          

CTE dates_in_interest

3. 0.051 0.052 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.012..0.052 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.150 0.150 ↑ 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.150 rows=31 loops=1)

7.          

CTE candidates

8. 15.043 77.554 ↑ 4,948.0 17,046 1

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

9. 47.031 62.511 ↑ 22,894.6 20,393 1

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

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

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

11. 0.194 0.194 ↑ 32.3 31 1

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

12. 11.160 11.160 ↑ 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.028..0.360 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. 14.581 7,055.186 ↑ 7.5 5,872 1

Sort (cost=14,858,164.19..14,858,274.63 rows=44,179 width=266) (actual time=7,054.775..7,055.186 rows=5,872 loops=1)

  • Sort Key: candidates.lockid, candidates.userid, candidates.buckets
  • Sort Method: quicksort Memory: 1752kB
14. 1,811.714 7,040.605 ↑ 7.5 5,872 1

Merge Join (cost=13,910,835.70..14,854,755.54 rows=44,179 width=266) (actual time=91.868..7,040.605 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: 2145880
15. 4,945.948 4,945.948 ↑ 1.0 4,211,110 1

Index Scan using opendoorrecord_lockid_userid_idx on opendoorrecord (cost=0.56..73,432.27 rows=4,217,194 width=51) (actual time=0.203..4,945.948 rows=4,211,110 loops=1)

16. 189.140 282.943 ↑ 39.2 2,151,647 1

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

17. 9.305 93.803 ↑ 4,948.0 17,046 1

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

  • Sort Key: candidates.lockid, candidates.userid
  • Sort Method: quicksort Memory: 5296kB
18. 84.498 84.498 ↑ 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=58.484..84.498 rows=17,046 loops=1)

Planning time : 0.441 ms
Execution time : 7,060.458 ms