explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dZCO

Settings
# exclusive inclusive rows x rows loops node
1. 1,124.468 1,958.728 ↓ 3.6 74,174 1

HashAggregate (cost=2,488.62..2,694.80 rows=20,618 width=31) (actual time=1,941.016..1,958.728 rows=74,174 loops=1)

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

CTE dates_in_interest

3. 0.019 0.020 ↑ 32.3 31 1

ProjectSet (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.010..0.020 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.076 0.076 ↑ 32.3 31 1

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

7.          

Initplan (for HashAggregate)

8. 0.001 0.109 ↑ 1.0 1 1

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

9. 0.014 0.108 ↑ 1,000.0 1 1

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

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

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

11. 0.001 0.017 ↑ 1.0 1 1

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

12. 0.009 0.016 ↑ 1,000.0 1 1

Sort (cost=25.00..27.50 rows=1,000 width=32) (actual time=0.016..0.016 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. 834.134 834.134 ↓ 95.0 2,002,923 1

Index Scan using opendoorrecord_opentime_idx on opendoorrecord (cost=0.43..2,258.14 rows=21,086 width=31) (actual time=0.163..834.134 rows=2,002,923 loops=1)

  • Index Cond: (((opentime)::text >= $2) AND ((opentime)::text < $3))
Planning time : 0.234 ms
Execution time : 1,962.765 ms