explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C2sd

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 24.664 ↑ 1.0 10 1

Limit (cost=128.44..128.46 rows=10 width=304) (actual time=24.660..24.664 rows=10 loops=1)

2.          

CTE margin_view

3. 3.695 23.128 ↓ 2.3 180 1

Hash Join (cost=66.70..125.23 rows=77 width=144) (actual time=22.558..23.128 rows=180 loops=1)

  • Hash Cond: (l_1.unique_product_id = l50apv.unique_product_id)
4. 1.679 1.679 ↑ 1.0 888 1

Seq Scan on listing l_1 (cost=0.00..55.99 rows=888 width=116) (actual time=1.308..1.679 rows=888 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 111
5. 0.044 17.754 ↓ 2.7 99 1

Hash (cost=66.24..66.24 rows=37 width=36) (actual time=17.754..17.754 rows=99 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
6. 0.021 17.710 ↓ 2.7 99 1

Subquery Scan on l50apv (cost=65.40..66.24 rows=37 width=36) (actual time=17.618..17.710 rows=99 loops=1)

7. 0.201 17.689 ↓ 2.7 99 1

HashAggregate (cost=65.40..65.87 rows=37 width=36) (actual time=17.616..17.689 rows=99 loops=1)

  • Group Key: most_recent_50_sales.unique_product_id
8.          

CTE sales_rank_per_uniq_prod

9. 0.177 17.353 ↑ 1.0 111 1

WindowAgg (cost=59.76..61.98 rows=111 width=26) (actual time=17.186..17.353 rows=111 loops=1)

10. 0.154 17.176 ↑ 1.0 111 1

Sort (cost=59.76..60.04 rows=111 width=18) (actual time=17.159..17.176 rows=111 loops=1)

  • Sort Key: l.unique_product_id, l.sold_at DESC
  • Sort Method: quicksort Memory: 33kB
11. 17.022 17.022 ↑ 1.0 111 1

Seq Scan on listing l (cost=0.00..55.99 rows=111 width=18) (actual time=0.024..17.022 rows=111 loops=1)

  • Filter: (sold_at IS NOT NULL)
  • Rows Removed by Filter: 888
12.          

CTE most_recent_50_sales

13. 17.430 17.430 ↓ 3.0 111 1

CTE Scan on sales_rank_per_uniq_prod (cost=0.00..2.50 rows=37 width=30) (actual time=17.191..17.430 rows=111 loops=1)

  • Filter: (recent_sale_rank < 51)
14. 17.488 17.488 ↓ 3.0 111 1

CTE Scan on most_recent_50_sales (cost=0.00..0.74 rows=37 width=22) (actual time=17.193..17.488 rows=111 loops=1)

15. 1.322 24.659 ↑ 7.7 10 1

Sort (cost=3.20..3.40 rows=77 width=304) (actual time=24.658..24.659 rows=10 loops=1)

  • Sort Key: margin_view.margin_usd DESC
  • Sort Method: top-N heapsort Memory: 28kB
16. 23.337 23.337 ↓ 2.3 180 1

CTE Scan on margin_view (cost=0.00..1.54 rows=77 width=304) (actual time=22.562..23.337 rows=180 loops=1)

Planning time : 4.298 ms
Execution time : 25.191 ms