explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yMw8 : bucket_quantity_held per holdable

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.011 0.558 ↑ 39,260.0 9 1

WindowAgg (cost=66,831.29..75,664.79 rows=353,340 width=152) (actual time=0.552..0.558 rows=9 loops=1)

2.          

CTE buckets

3. 0.001 0.033 ↑ 453.0 3 1

Limit (cost=155.40..179.18 rows=1,359 width=16) (actual time=0.030..0.033 rows=3 loops=1)

4. 0.007 0.032 ↑ 340.0 4 1

WindowAgg (cost=155.38..179.18 rows=1,360 width=16) (actual time=0.028..0.032 rows=4 loops=1)

5. 0.004 0.025 ↑ 340.0 4 1

Sort (cost=155.38..158.78 rows=1,360 width=8) (actual time=0.024..0.025 rows=4 loops=1)

  • Sort Key: holds_1.starts_at
  • Sort Method: quicksort Memory: 25kB
6. 0.010 0.021 ↑ 340.0 4 1

HashAggregate (cost=57.40..71.00 rows=1,360 width=8) (actual time=0.017..0.021 rows=4 loops=1)

  • Group Key: holds_1.starts_at
7. 0.001 0.011 ↑ 170.0 8 1

Append (cost=0.00..54.00 rows=1,360 width=8) (actual time=0.007..0.011 rows=8 loops=1)

8. 0.008 0.008 ↑ 170.0 4 1

Seq Scan on holds holds_1 (cost=0.00..16.80 rows=680 width=8) (actual time=0.007..0.008 rows=4 loops=1)

9. 0.002 0.002 ↑ 170.0 4 1

Seq Scan on holds holds_2 (cost=0.00..16.80 rows=680 width=8) (actual time=0.002..0.002 rows=4 loops=1)

10. 0.008 0.547 ↑ 39,260.0 9 1

Sort (cost=66,652.10..67,535.45 rows=353,340 width=92) (actual time=0.546..0.547 rows=9 loops=1)

  • Sort Key: buckets.bucket_starts_at, buckets.bucket_ends_at, holdings.holdable_type, holdings.holdable_id
  • Sort Method: quicksort Memory: 26kB
11. 0.006 0.539 ↑ 39,260.0 9 1

Nested Loop (cost=25.30..15,974.59 rows=353,340 width=92) (actual time=0.526..0.539 rows=9 loops=1)

  • Join Filter: "overlaps"(buckets.bucket_starts_at, buckets.bucket_ends_at, holds.starts_at, holds.ends_at)
  • Rows Removed by Join Filter: 6
12. 0.035 0.035 ↑ 453.0 3 1

CTE Scan on buckets (cost=0.00..27.18 rows=1,359 width=16) (actual time=0.031..0.035 rows=3 loops=1)

13. 0.003 0.498 ↑ 156.0 5 3

Materialize (cost=25.30..49.06 rows=780 width=76) (actual time=0.165..0.166 rows=5 loops=3)

14. 0.484 0.495 ↑ 156.0 5 1

Hash Join (cost=25.30..45.16 rows=780 width=76) (actual time=0.492..0.495 rows=5 loops=1)

  • Hash Cond: (holdings.hold_id = holds.id)
15. 0.003 0.003 ↑ 156.0 5 1

Seq Scan on holdings (cost=0.00..17.80 rows=780 width=60) (actual time=0.002..0.003 rows=5 loops=1)

16. 0.003 0.008 ↑ 170.0 4 1

Hash (cost=16.80..16.80 rows=680 width=24) (actual time=0.007..0.008 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.005 0.005 ↑ 170.0 4 1

Seq Scan on holds (cost=0.00..16.80 rows=680 width=24) (actual time=0.004..0.005 rows=4 loops=1)

Planning time : 0.204 ms
Execution time : 0.616 ms