explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eBJH

Settings
# exclusive inclusive rows x rows loops node
1. 825.610 5,578.588 ↑ 39.9 349 1

Merge Join (cost=95,724,569.77..96,102,495.47 rows=13,931 width=291) (actual time=101.983..5,578.588 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.060 0.062 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.013..0.062 rows=31 loops=1)

4. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)

5.          

CTE buckets

6. 0.184 0.184 ↑ 32.3 31 1

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

7.          

CTE candidates_r2

8. 15.937 81.480 ↑ 58,028.3 485 1

GroupAggregate (cost=84,634,743.19..91,530,937.36 rows=28,143,733 width=119) (actual time=62.066..81.480 rows=485 loops=1)

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

Sort (cost=84,634,743.19..85,802,904.03 rows=467,264,333 width=119) (actual time=62.034..65.543 rows=20,398 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6187kB
10. 3.874 15.587 ↑ 22,907.4 20,398 1

Nested Loop (cost=0.43..14,064,012.60 rows=467,264,333 width=119) (actual time=0.060..15.587 rows=20,398 loops=1)

11. 0.243 0.243 ↑ 32.3 31 1

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

12. 11.470 11.470 ↑ 710.1 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..9,391.35 rows=467,264 width=51) (actual time=0.022..0.370 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,663.063 4,663.063 ↑ 1.0 4,203,446 1

Index Scan using opendoorrecord_lockid_userid_idx on opendoorrecord (cost=0.56..73,500.39 rows=4,221,562 width=103) (actual time=0.115..4,663.063 rows=4,203,446 loops=1)

14. 7.604 89.915 ↑ 327.4 85,951 1

Materialize (cost=4,193,556.82..4,334,275.48 rows=28,143,733 width=188) (actual time=81.922..89.915 rows=85,951 loops=1)

15. 0.579 82.311 ↑ 58,028.3 485 1

Sort (cost=4,193,556.82..4,263,916.15 rows=28,143,733 width=188) (actual time=81.919..82.311 rows=485 loops=1)

  • Sort Key: candidates_r2.lockid, candidates_r2.userid
  • Sort Method: quicksort Memory: 153kB
16. 81.732 81.732 ↑ 58,028.3 485 1

CTE Scan on candidates_r2 (cost=0.00..562,874.66 rows=28,143,733 width=188) (actual time=62.069..81.732 rows=485 loops=1)

Planning time : 0.535 ms
Execution time : 5,578.753 ms