explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nOkD : Optimization for: Optimization for: plan #81H1; plan #r2Xf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 263.001 3,476.774 ↑ 1.5 113,401 1

Hash Join (cost=112,375.96..142,997.95 rows=172,372 width=279) (actual time=1,384.456..3,476.774 rows=113,401 loops=1)

  • Hash Cond: (ebay_listings.isbn = asins.product_code)
2.          

Initplan (forHash Join)

3. 0.002 0.016 ↑ 1.0 1 1

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

4.          

Initplan (forResult)

5. 0.001 0.014 ↑ 1.0 1 1

Limit (cost=0.15..0.20 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

6. 0.013 0.013 ↑ 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.012..0.013 rows=1 loops=1)

  • Index Cond: (id IS NOT NULL)
  • Heap Fetches: 1
7. 605.885 2,859.529 ↓ 1.9 323,002 1

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

  • Hash Cond: (listing_prices.listing_id = ebay_listings.id)
8. 134.118 1,829.719 ↓ 1.8 323,002 1

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

9. 302.588 726.595 ↓ 1,615.0 323,002 1

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

  • Group Key: max(listing_prices_1.id)
10. 121.781 424.007 ↑ 1.0 323,002 1

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

  • Group Key: listing_prices_1.listing_id
11. 256.461 302.226 ↑ 1.1 332,870 1

Sort (cost=45,039.42..45,918.04 rows=351,447 width=16) (actual time=233.872..302.226 rows=332,870 loops=1)

  • Sort Key: listing_prices_1.listing_id
  • Sort Method: external merge Disk: 8488kB
12. 45.765 45.765 ↑ 1.1 332,870 1

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

13. 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)))
14. 266.377 423.925 ↑ 1.0 323,002 1

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

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

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

16. 9.910 354.228 ↑ 3.2 26,157 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 1288kB
17. 218.592 344.318 ↑ 3.2 26,157 1

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

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

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

  • Filter: (search_id = $1)
19. 23.254 35.931 ↑ 1.0 83,148 1

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

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

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