explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2h9q

Settings
# exclusive inclusive rows x rows loops node
1. 583.611 9,487.637 ↓ 1.9 147,334 1

GroupAggregate (cost=241,233.44..248,299.43 rows=76,044 width=105) (actual time=8,660.710..9,487.637 rows=147,334 loops=1)

  • Group Key: b.id, b.fruit_box, b.status, b.production_date, b.number, b.production_site, b.shipper_name, b.product_type
2. 3,511.790 8,904.026 ↓ 2.0 416,709 1

Sort (cost=241,233.44..241,743.06 rows=203,848 width=105) (actual time=8,660.655..8,904.026 rows=416,709 loops=1)

  • Sort Key: b.id, b.fruit_box, b.status, b.production_date, b.number, b.production_site, b.shipper_name, b.product_type
  • Sort Method: external merge Disk: 49360kB
3. 375.383 5,392.236 ↓ 2.0 416,709 1

Hash Join (cost=332.81..222,579.97 rows=203,848 width=105) (actual time=7.681..5,392.236 rows=416,709 loops=1)

  • Hash Cond: (r.ms_recipe_id = recipes.ms_recipe_id)
4. 277.706 5,009.336 ↓ 2.0 416,709 1

Nested Loop (cost=1.12..217,916.51 rows=203,848 width=56) (actual time=0.141..5,009.336 rows=416,709 loops=1)

5. 458.944 458.944 ↓ 3.9 147,334 1

Index Scan using boxes_fc_country_production_site_production_date_index on boxes b (cost=0.56..4,780.04 rows=38,022 width=48) (actual time=0.092..458.944 rows=147,334 loops=1)

  • Index Cond: ((fc_country = 'us'::text) AND (production_site = ANY ('{NYC1,TEX1,CA1}'::text[])) AND (production_date >= '2018-12-29'::date) AND (production_date <= '2019-02-05'::date))
  • Filter: (status = ANY ('{placed,complete}'::text[]))
6. 4,272.686 4,272.686 ↑ 45.7 3 147,334

Index Scan using ordered_recipes_shipment_number_ms_recipe_id_index on ordered_recipes r (cost=0.56..4.24 rows=137 width=25) (actual time=0.027..0.029 rows=3 loops=147,334)

  • Index Cond: (shipment_number = b.number)
7. 3.513 7.517 ↓ 1.0 13,647 1

Hash (cost=161.14..161.14 rows=13,644 width=57) (actual time=7.517..7.517 rows=13,647 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1208kB
8. 4.004 4.004 ↓ 1.0 13,647 1

Seq Scan on recipes (cost=0.00..161.14 rows=13,644 width=57) (actual time=0.019..4.004 rows=13,647 loops=1)