explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r2Xf : Optimization for: plan #81H1

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 240.857 3,811.321 ↑ 1.5 113,401 1

Sort (cost=180,379.65..180,810.58 rows=172,372 width=279) (actual time=3,763.128..3,811.321 rows=113,401 loops=1)

  • Sort Key: bestseller_ranks.rank
  • Sort Method: external merge Disk: 33064kB
2.          

Initplan (forSort)

3. 0.001 0.033 ↑ 1.0 1 1

Result (cost=0.20..0.21 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=1)

4.          

Initplan (forResult)

5. 0.002 0.032 ↑ 1.0 1 1

Limit (cost=0.15..0.20 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=1)

6. 0.030 0.030 ↑ 1,443.0 1 1

Index Only Scan Backward using bestseller_search_pkey on bestseller_search (cost=0.15..73.41 rows=1,443 width=8) (actual time=0.030..0.030 rows=1 loops=1)

  • Index Cond: (id IS NOT NULL)
  • Heap Fetches: 1
7. 285.262 3,570.431 ↑ 1.5 113,401 1

Hash Join (cost=112,375.74..142,997.74 rows=172,372 width=279) (actual time=1,391.741..3,570.431 rows=113,401 loops=1)

  • Hash Cond: (ebay_listings.isbn = asins.product_code)
8. 646.718 2,907.077 ↓ 1.9 323,002 1

Hash Join (cost=90,196.08..105,071.03 rows=172,372 width=268) (actual time=1,012.221..2,907.077 rows=323,002 loops=1)

  • Hash Cond: (listing_prices.listing_id = ebay_listings.id)
9. 156.321 1,811.588 ↓ 1.8 323,002 1

Nested Loop (cost=55,084.54..56,424.20 rows=175,724 width=12) (actual time=562.906..1,811.588 rows=323,002 loops=1)

10. 276.926 686.261 ↓ 1,615.0 323,002 1

HashAggregate (cost=55,084.12..55,086.12 rows=200 width=8) (actual time=562.846..686.261 rows=323,002 loops=1)

  • Group Key: max(listing_prices_1.id)
11. 103.595 409.335 ↑ 1.0 323,002 1

GroupAggregate (cost=45,039.42..50,968.09 rows=329,282 width=16) (actual time=246.388..409.335 rows=323,002 loops=1)

  • Group Key: listing_prices_1.listing_id
12. 258.085 305.740 ↑ 1.1 332,869 1

Sort (cost=45,039.42..45,918.04 rows=351,447 width=16) (actual time=246.378..305.740 rows=332,869 loops=1)

  • Sort Key: listing_prices_1.listing_id
  • Sort Method: external merge Disk: 8488kB
13. 47.655 47.655 ↑ 1.1 332,869 1

Seq Scan on listing_prices listing_prices_1 (cost=0.00..6,656.47 rows=351,447 width=16) (actual time=0.014..47.655 rows=332,869 loops=1)

14. 969.006 969.006 ↑ 1.0 1 323,002

Index Scan using listing_prices_pkey on listing_prices (cost=0.42..7.94 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=323,002)

  • Index Cond: (id = (max(listing_prices_1.id)))
15. 283.401 448.771 ↑ 1.0 323,002 1

Hash (cost=19,718.02..19,718.02 rows=323,002 width=264) (actual time=448.771..448.771 rows=323,002 loops=1)

  • Buckets: 16384 Batches: 32 Memory Usage: 3059kB
16. 165.370 165.370 ↑ 1.0 323,002 1

Seq Scan on ebay_listings (cost=0.00..19,718.02 rows=323,002 width=264) (actual time=0.141..165.370 rows=323,002 loops=1)

17. 13.066 378.092 ↑ 3.2 26,157 1

Hash (cost=20,652.31..20,652.31 rows=83,148 width=23) (actual time=378.092..378.092 rows=26,157 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 1288kB
18. 237.922 365.026 ↑ 3.2 26,157 1

Hash Join (cost=3,131.83..20,652.31 rows=83,148 width=23) (actual time=37.873..365.026 rows=26,157 loops=1)

  • Hash Cond: ((bestseller_ranks.asin)::text = (asins.asin)::text)
19. 89.617 89.617 ↑ 1.0 500,000 1

Seq Scan on bestseller_ranks (cost=0.00..9,442.00 rows=500,000 width=15) (actual time=0.051..89.617 rows=500,000 loops=1)

  • Filter: (search_id = $1)
20. 24.104 37.487 ↑ 1.0 83,148 1

Hash (cost=1,604.48..1,604.48 rows=83,148 width=19) (actual time=37.487..37.487 rows=83,148 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2793kB
21. 13.383 13.383 ↑ 1.0 83,148 1

Seq Scan on asins (cost=0.00..1,604.48 rows=83,148 width=19) (actual time=0.007..13.383 rows=83,148 loops=1)