explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XKi8 : Optimization for: plan #C2sd

Settings

Optimization path:

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

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

2.          

CTE margin_view

3. 0.345 45.766 ↓ 2.3 180 1

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

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

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

  • Filter: is_active
  • Rows Removed by Filter: 111
5. 0.035 44.822 ↓ 2.7 99 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
6. 0.025 44.787 ↓ 2.7 99 1

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

7. 42.794 44.762 ↓ 2.7 99 1

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

  • Group Key: most_recent_50_sales.unique_product_id
8.          

CTE sales_rank_per_uniq_prod

9. 1.482 1.813 ↑ 1.0 111 1

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

10. 0.064 0.331 ↑ 1.0 111 1

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

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

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

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

CTE most_recent_50_sales

13. 1.904 1.904 ↓ 3.0 111 1

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

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

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

15. 2.725 48.713 ↑ 7.7 10 1

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

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

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

Planning time : 86.105 ms
Execution time : 48.819 ms