explain.depesz.com

PostgreSQL's explain analyze made readable

Result: is1d

Settings
# exclusive inclusive rows x rows loops node
1. 4.015 3,865.877 ↑ 12.0 3,667 1

Unique (cost=104,598,537.49..104,598,979.00 rows=44,151 width=266) (actual time=3,861.341..3,865.877 rows=3,667 loops=1)

2.          

CTE dates_in_interest

3. 0.130 0.131 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.012..0.131 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.337 0.337 ↑ 32.3 31 1

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

7.          

CTE candidates

8. 14.910 212.645 ↑ 4,946.5 17,046 1

Unique (cost=85,082,061.79..89,749,491.79 rows=84,317,200 width=139) (actual time=193.801..212.645 rows=17,046 loops=1)

9. 46.334 197.735 ↑ 22,887.4 20,393 1

Sort (cost=85,082,061.79..86,248,919.29 rows=466,743,000 width=139) (actual time=193.799..197.735 rows=20,393 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6185kB
10. 4.261 151.401 ↑ 22,887.4 20,393 1

Nested Loop (cost=0.43..14,046,862.00 rows=466,743,000 width=139) (actual time=5.169..151.401 rows=20,393 loops=1)

11. 0.417 0.417 ↑ 32.3 31 1

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

12. 146.723 146.723 ↑ 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=2.320..4.733 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.817 3,861.862 ↑ 7.5 5,872 1

Sort (cost=14,848,970.65..14,849,081.03 rows=44,151 width=266) (actual time=3,861.339..3,861.862 rows=5,872 loops=1)

  • Sort Key: candidates.lockid, candidates.userid, candidates.buckets
  • Sort Method: quicksort Memory: 1752kB
14. 1,429.948 3,848.045 ↑ 7.5 5,872 1

Merge Join (cost=13,906,256.79..14,845,564.37 rows=44,151 width=266) (actual time=224.821..3,848.045 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: 2145256
15. 2,021.789 2,021.789 ↑ 1.0 4,209,703 1

Index Only Scan using opendoorrecord_lockid_userid_opentime_openmode_idx on opendoorrecord (cost=0.56..69,096.42 rows=4,215,864 width=51) (actual time=0.043..2,021.789 rows=4,209,703 loops=1)

  • Heap Fetches: 1047332
16. 169.615 396.308 ↑ 39.2 2,151,023 1

Materialize (cost=13,906,256.23..14,327,842.23 rows=84,317,200 width=246) (actual time=224.220..396.308 rows=2,151,023 loops=1)

17. 8.454 226.693 ↑ 4,946.5 17,046 1

Sort (cost=13,906,256.23..14,117,049.23 rows=84,317,200 width=246) (actual time=224.217..226.693 rows=17,046 loops=1)

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

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

Planning time : 0.405 ms
Execution time : 3,867.083 ms