explain.depesz.com

PostgreSQL's explain analyze made readable

Result: obDF

Settings
# exclusive inclusive rows x rows loops node
1. 3.801 13,484.759 ↑ 10.1 3,667 1

Unique (cost=95,646,044.36..95,646,414.17 rows=36,981 width=266) (actual time=13,480.544..13,484.759 rows=3,667 loops=1)

2.          

CTE dates_in_interest

3. 0.038 0.039 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.009..0.039 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.126 0.126 ↑ 32.3 31 1

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

7.          

CTE candidates

8. 13.432 70.751 ↑ 4,527.8 17,043 1

Unique (cost=77,595,560.10..81,867,223.43 rows=77,167,800 width=139) (actual time=53.875..70.751 rows=17,043 loops=1)

9. 43.620 57.319 ↑ 20,949.8 20,390 1

Sort (cost=77,595,560.10..78,663,475.93 rows=427,166,333 width=139) (actual time=53.873..57.319 rows=20,390 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6185kB
10. 3.693 13.699 ↑ 20,949.8 20,390 1

Nested Loop (cost=0.43..12,856,691.40 rows=427,166,333 width=139) (actual time=0.063..13.699 rows=20,390 loops=1)

11. 0.179 0.179 ↑ 32.3 31 1

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

12. 9.827 9.827 ↑ 649.2 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..8,585.01 rows=427,166 width=51) (actual time=0.018..0.317 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. 7.616 13,480.958 ↑ 6.3 5,872 1

Sort (cost=13,778,745.89..13,778,838.35 rows=36,981 width=266) (actual time=13,480.543..13,480.958 rows=5,872 loops=1)

  • Sort Key: candidates.lockid, candidates.userid, candidates.buckets
  • Sort Method: quicksort Memory: 1752kB
14. 1,482.940 13,473.342 ↑ 6.3 5,872 1

Merge Join (cost=13,153,605.22..13,775,940.05 rows=36,981 width=266) (actual time=9,303.161..13,473.342 rows=5,872 loops=1)

  • Merge Cond: (((candidates.lockid)::text = (opendoorrecord.lockid)::text) AND ((candidates.userid)::text = (opendoorrecord.userid)::text))
  • Join Filter: (((opendoorrecord.opentime)::text >= (candidates.buckets).r1_from) AND ((opendoorrecord.opentime)::text < (candidates.buckets).r1_to))
  • Rows Removed by Join Filter: 2129569
15. 6.904 83.339 ↑ 4,527.8 17,043 1

Sort (cost=12,677,800.08..12,870,719.58 rows=77,167,800 width=246) (actual time=81.688..83.339 rows=17,043 loops=1)

  • Sort Key: candidates.lockid, candidates.userid
  • Sort Method: quicksort Memory: 5296kB
16. 76.435 76.435 ↑ 4,527.8 17,043 1

CTE Scan on candidates (cost=0.00..1,543,356.00 rows=77,167,800 width=246) (actual time=53.877..76.435 rows=17,043 loops=1)

17. 734.885 11,907.063 ↓ 1.5 5,603,322 1

Materialize (cost=475,805.14..495,097.08 rows=3,858,387 width=51) (actual time=9,220.666..11,907.063 rows=5,603,322 loops=1)

18. 9,503.934 11,172.178 ↓ 1.1 4,171,915 1

Sort (cost=475,805.14..485,451.11 rows=3,858,387 width=51) (actual time=9,220.661..11,172.178 rows=4,171,915 loops=1)

  • Sort Key: opendoorrecord.lockid, opendoorrecord.userid
  • Sort Method: external merge Disk: 255144kB
19. 1,668.244 1,668.244 ↓ 1.1 4,178,059 1

Seq Scan on opendoorrecord (cost=0.00..46,169.97 rows=3,858,387 width=51) (actual time=0.017..1,668.244 rows=4,178,059 loops=1)

Planning time : 0.300 ms
Execution time : 13,490.046 ms