explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HQwn

Settings
# exclusive inclusive rows x rows loops node
1. 993.795 1,715.457 ↓ 3.6 74,174 1

HashAggregate (cost=1,120.51..1,326.77 rows=20,626 width=31) (actual time=1,697.810..1,715.457 rows=74,174 loops=1)

  • Group Key: opendoorrecord.lockid, opendoorrecord.userid
2.          

CTE dates_in_interest

3. 0.021 0.022 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.011..0.022 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.000..0.001 rows=1 loops=1)

5.          

CTE buckets

6. 0.189 0.189 ↑ 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.189 rows=31 loops=1)

7.          

Initplan (for HashAggregate)

8. 0.001 0.225 ↑ 1.0 1 1

Limit (cost=25.00..25.00 rows=1 width=32) (actual time=0.225..0.225 rows=1 loops=1)

9. 0.016 0.224 ↑ 1,000.0 1 1

Sort (cost=25.00..27.50 rows=1,000 width=32) (actual time=0.224..0.224 rows=1 loops=1)

  • Sort Key: buckets.r1_from
  • Sort Method: top-N heapsort Memory: 25kB
10. 0.208 0.208 ↑ 32.3 31 1

CTE Scan on buckets (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.020..0.208 rows=31 loops=1)

11. 0.000 0.018 ↑ 1.0 1 1

Limit (cost=25.00..25.00 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=1)

12. 0.011 0.018 ↑ 1,000.0 1 1

Sort (cost=25.00..27.50 rows=1,000 width=32) (actual time=0.018..0.018 rows=1 loops=1)

  • Sort Key: buckets_1.r2_to DESC
  • Sort Method: top-N heapsort Memory: 25kB
13. 0.007 0.007 ↑ 32.3 31 1

CTE Scan on buckets buckets_1 (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.001..0.007 rows=31 loops=1)

14. 721.419 721.419 ↓ 95.0 2,002,923 1

Index Only Scan using opendoorrecord_opentime_lockid_userid_idx on opendoorrecord (cost=0.56..889.99 rows=21,094 width=31) (actual time=0.304..721.419 rows=2,002,923 loops=1)

  • Index Cond: ((opentime >= $2) AND (opentime < $3))
  • Heap Fetches: 871444
Planning time : 0.345 ms
Execution time : 1,719.364 ms