explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 81H1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 231.889 4,330.827 ↑ 1.5 113,401 1

Sort (cost=181,116.92..181,551.28 rows=173,744 width=279) (actual time=4,289.309..4,330.827 rows=113,401 loops=1)

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

Initplan (forSort)

3. 0.002 0.969 ↑ 1.0 1 1

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

4.          

Initplan (forResult)

5. 0.002 0.967 ↑ 1.0 1 1

Limit (cost=0.15..0.20 rows=1 width=8) (actual time=0.966..0.967 rows=1 loops=1)

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

  • Index Cond: (id IS NOT NULL)
  • Heap Fetches: 1
7. 279.024 4,097.969 ↑ 1.5 113,401 1

Hash Join (cost=112,588.95..143,427.24 rows=173,744 width=279) (actual time=1,781.267..4,097.969 rows=113,401 loops=1)

  • Hash Cond: (ebay_listings.isbn = asins.product_code)
8. 643.164 3,260.959 ↓ 1.9 323,002 1

Hash Join (cost=90,343.95..105,308.90 rows=173,744 width=268) (actual time=1,182.997..3,260.959 rows=323,002 loops=1)

  • Hash Cond: (listing_prices.listing_id = ebay_listings.id)
9. 32.075 2,015.690 ↓ 1.8 323,002 1

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

10. 285.490 691.607 ↓ 1,615.0 323,002 1

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

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

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

  • Group Key: listing_prices_1.listing_id
12. 259.398 304.683 ↑ 1.1 332,869 1

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

  • Sort Key: listing_prices_1.listing_id
  • Sort Method: external merge Disk: 8488kB
13. 45.285 45.285 ↑ 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.013..45.285 rows=332,869 loops=1)

14. 1,292.008 1,292.008 ↑ 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.004..0.004 rows=1 loops=323,002)

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

Hash (cost=19,743.74..19,743.74 rows=325,574 width=264) (actual time=602.105..602.105 rows=323,002 loops=1)

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

Seq Scan on ebay_listings (cost=0.00..19,743.74 rows=325,574 width=264) (actual time=0.039..311.660 rows=323,002 loops=1)

17. 15.491 557.986 ↑ 3.2 26,157 1

Hash (cost=20,698.50..20,698.50 rows=84,200 width=23) (actual time=557.986..557.986 rows=26,157 loops=1)

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

Hash Join (cost=3,161.50..20,698.50 rows=84,200 width=23) (actual time=68.590..542.495 rows=26,157 loops=1)

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

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

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

Hash (cost=1,615.00..1,615.00 rows=84,200 width=19) (actual time=66.852..66.852 rows=83,148 loops=1)

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

Seq Scan on asins (cost=0.00..1,615.00 rows=84,200 width=19) (actual time=0.007..42.062 rows=83,148 loops=1)