explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tzf6

Settings
# exclusive inclusive rows x rows loops node
1. 5.608 13,038.593 ↑ 12.0 3,667 1

Unique (cost=104,586,264.10..104,586,705.51 rows=44,141 width=266) (actual time=13,032.322..13,038.593 rows=3,667 loops=1)

2.          

CTE dates_in_interest

3. 3.154 3.156 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=2.546..3.156 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. 3.618 3.618 ↑ 32.3 31 1

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

7.          

CTE candidates

8. 16.636 1,274.741 ↑ 4,945.9 17,046 1

Unique (cost=85,072,058.59..89,738,960.81 rows=84,307,800 width=139) (actual time=1,253.679..1,274.741 rows=17,046 loops=1)

9. 132.797 1,258.105 ↑ 22,884.8 20,393 1

Sort (cost=85,072,058.59..86,238,784.14 rows=466,690,222 width=139) (actual time=1,253.676..1,258.105 rows=20,393 loops=1)

  • Sort Key: opendoorrecord_1.lockid, opendoorrecord_1.userid, buckets.*
  • Sort Method: quicksort Memory: 6185kB
10. 5.656 1,125.308 ↑ 22,884.8 20,393 1

Nested Loop (cost=0.43..14,045,271.80 rows=466,690,222 width=139) (actual time=7.401..1,125.308 rows=20,393 loops=1)

11. 3.745 3.745 ↑ 32.3 31 1

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

12. 1,115.907 1,115.907 ↑ 709.3 658 31

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.43..9,378.35 rows=466,690 width=51) (actual time=2.604..35.997 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. 23.110 13,032.985 ↑ 7.5 5,872 1

Sort (cost=14,847,228.25..14,847,338.60 rows=44,141 width=266) (actual time=13,032.321..13,032.985 rows=5,872 loops=1)

  • Sort Key: candidates.lockid, candidates.userid, candidates.buckets
  • Sort Method: quicksort Memory: 1752kB
14. 2,130.880 13,009.875 ↑ 7.5 5,872 1

Merge Join (cost=13,904,638.71..14,843,822.81 rows=44,141 width=266) (actual time=1,401.069..13,009.875 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: 2145125
15. 9,339.131 9,339.131 ↑ 1.0 4,209,295 1

Index Only Scan using opendoorrecord_lockid_userid_openmode_opentime_idx on opendoorrecord (cost=0.56..69,070.66 rows=4,215,387 width=51) (actual time=2.258..9,339.131 rows=4,209,295 loops=1)

  • Heap Fetches: 1046931
16. 244.144 1,539.864 ↑ 39.2 2,150,892 1

Materialize (cost=13,904,638.15..14,326,177.15 rows=84,307,800 width=246) (actual time=1,290.459..1,539.864 rows=2,150,892 loops=1)

17. 13.480 1,295.720 ↑ 4,945.9 17,046 1

Sort (cost=13,904,638.15..14,115,407.65 rows=84,307,800 width=246) (actual time=1,290.454..1,295.720 rows=17,046 loops=1)

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

CTE Scan on candidates (cost=0.00..1,686,156.00 rows=84,307,800 width=246) (actual time=1,253.681..1,282.240 rows=17,046 loops=1)

Planning time : 1.508 ms
Execution time : 13,040.418 ms