explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Warv9 : Optimization for: max_quantity_held per holdable; plan #OmP8 - with 3 indexes

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.013 1.188 ↑ 1.0 5 1

Seq Scan on holdings (cost=0.00..31.85 rows=5 width=84) (actual time=1.126..1.188 rows=5 loops=1)

2.          

SubPlan (forSeq Scan)

3. 0.010 1.175 ↑ 1.0 1 5

Aggregate (cost=6.15..6.16 rows=1 width=8) (actual time=0.235..0.235 rows=1 loops=5)

4. 0.005 1.165 ↓ 2.0 2 5

Subquery Scan on bucketed_holdings (cost=5.70..6.15 rows=1 width=8) (actual time=0.229..0.233 rows=2 loops=5)

  • Filter: (bucketed_holdings.holding_id = holdings.id)
  • Rows Removed by Filter: 7
5. 0.030 1.160 ↑ 1.3 9 5

WindowAgg (cost=5.70..6.00 rows=12 width=152) (actual time=0.228..0.232 rows=9 loops=5)

6.          

CTE buckets

7. 0.001 0.053 ↑ 2.3 3 1

Limit (cost=2.52..2.64 rows=7 width=16) (actual time=0.049..0.053 rows=3 loops=1)

8. 0.009 0.052 ↑ 2.0 4 1

WindowAgg (cost=2.50..2.64 rows=8 width=16) (actual time=0.048..0.052 rows=4 loops=1)

9. 0.016 0.043 ↑ 2.0 4 1

Sort (cost=2.50..2.52 rows=8 width=8) (actual time=0.043..0.043 rows=4 loops=1)

  • Sort Key: holds.starts_at
  • Sort Method: quicksort Memory: 25kB
10. 0.005 0.027 ↑ 2.0 4 1

HashAggregate (cost=2.22..2.30 rows=8 width=8) (actual time=0.026..0.027 rows=4 loops=1)

  • Group Key: holds.starts_at
11. 0.001 0.022 ↑ 1.0 8 1

Append (cost=0.00..2.20 rows=8 width=8) (actual time=0.003..0.022 rows=8 loops=1)

12. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on holds (cost=0.00..1.04 rows=4 width=8) (actual time=0.003..0.004 rows=4 loops=1)

13. 0.017 0.017 ↑ 1.0 4 1

Seq Scan on holds holds_1 (cost=0.00..1.04 rows=4 width=8) (actual time=0.010..0.017 rows=4 loops=1)

14. 0.025 1.130 ↑ 1.3 9 5

Sort (cost=3.06..3.09 rows=12 width=68) (actual time=0.225..0.226 rows=9 loops=5)

  • Sort Key: buckets.bucket_starts_at, buckets.bucket_ends_at, holdings_1.holdable_type, holdings_1.holdable_id
  • Sort Method: quicksort Memory: 26kB
15. 0.015 1.105 ↑ 1.3 9 5

Nested Loop (cost=1.09..2.84 rows=12 width=68) (actual time=0.216..0.221 rows=9 loops=5)

  • Join Filter: "overlaps"(buckets.bucket_starts_at, buckets.bucket_ends_at, holds_2.starts_at, holds_2.ends_at)
  • Rows Removed by Join Filter: 6
16. 0.055 0.055 ↑ 2.3 3 5

CTE Scan on buckets (cost=0.00..0.14 rows=7 width=16) (actual time=0.010..0.011 rows=3 loops=5)

17. 0.008 1.035 ↑ 1.0 5 15

Materialize (cost=1.09..2.19 rows=5 width=68) (actual time=0.068..0.069 rows=5 loops=15)

18. 1.006 1.027 ↑ 1.0 5 1

Hash Join (cost=1.09..2.16 rows=5 width=68) (actual time=1.024..1.027 rows=5 loops=1)

  • Hash Cond: (holdings_1.hold_id = holds_2.id)
19. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on holdings holdings_1 (cost=0.00..1.05 rows=5 width=60) (actual time=0.002..0.002 rows=5 loops=1)

20. 0.003 0.019 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=24) (actual time=0.019..0.019 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.016 0.016 ↑ 1.0 4 1

Seq Scan on holds holds_2 (cost=0.00..1.04 rows=4 width=24) (actual time=0.015..0.016 rows=4 loops=1)

Planning time : 0.453 ms
Execution time : 1.424 ms