explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ina1

Settings
# exclusive inclusive rows x rows loops node
1. 191.587 10,826.690 ↑ 32.8 128 1

GroupAggregate (cost=263,663.12..263,973.89 rows=4,200 width=145) (actual time=10,492.516..10,826.690 rows=128 loops=1)

  • Group Key: pd.production_date, b.production_site
2.          

CTE production_dates

3. 1.354 27.560 ↓ 3.0 62 1

HashAggregate (cost=832.01..832.22 rows=21 width=4) (actual time=27.551..27.560 rows=62 loops=1)

  • Group Key: pd_1.production_date
4. 5.438 26.206 ↓ 1.8 4,304 1

Hash Join (cost=102.77..826.05 rows=2,386 width=4) (actual time=2.796..26.206 rows=4,304 loops=1)

  • Hash Cond: (rd.ms_recipe_id = r.ms_recipe_id)
5. 5.277 18.239 ↓ 1.5 18,797 1

Nested Loop (cost=0.29..635.98 rows=12,744 width=8) (actual time=0.183..18.239 rows=18,797 loops=1)

6. 1.826 1.826 ↑ 1.0 116 1

Seq Scan on production_dates pd_1 (cost=0.00..164.99 rows=120 width=8) (actual time=0.167..1.826 rows=116 loops=1)

  • Filter: ((production_date >= '2018-12-29'::date) AND (fc_country = 'us'::text))
  • Rows Removed by Filter: 3910
7. 11.136 11.136 ↓ 1.5 162 116

Index Scan using recipe_delivery_dates_delivery_date_index on recipe_delivery_dates rd (cost=0.29..2.85 rows=107 width=8) (actual time=0.007..0.096 rows=162 loops=116)

  • Index Cond: (delivery_date = pd_1.delivery_date)
8. 0.509 2.529 ↑ 1.0 2,554 1

Hash (cost=70.56..70.56 rows=2,554 width=4) (actual time=2.529..2.529 rows=2,554 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 90kB
9. 2.020 2.020 ↑ 1.0 2,554 1

Index Scan using recipes_country_index on recipes r (cost=0.29..70.56 rows=2,554 width=4) (actual time=0.030..2.020 rows=2,554 loops=1)

  • Index Cond: (country = 'us'::text)
10.          

CTE production_site_boxes_with_dishbags

11. 650.212 9,334.322 ↓ 1.7 149,259 1

GroupAggregate (cost=252,423.92..260,110.38 rows=85,806 width=105) (actual time=8,428.599..9,334.322 rows=149,259 loops=1)

  • Group Key: b_1.id, b_1.fruit_box, b_1.status, b_1.production_date, b_1.number, b_1.production_site, b_1.shipper_name, b_1.product_type
12. 3,525.751 8,684.110 ↓ 1.9 422,107 1

Sort (cost=252,423.92..252,975.08 rows=220,463 width=105) (actual time=8,428.558..8,684.110 rows=422,107 loops=1)

  • Sort Key: b_1.id, b_1.fruit_box, b_1.status, b_1.production_date, b_1.number, b_1.production_site, b_1.shipper_name, b_1.product_type
  • Sort Method: external merge Disk: 50128kB
13. 339.874 5,158.359 ↓ 1.9 422,107 1

Hash Left Join (cost=332.81..232,125.43 rows=220,463 width=105) (actual time=8.319..5,158.359 rows=422,107 loops=1)

  • Hash Cond: (r_1.ms_recipe_id = recipes.ms_recipe_id)
14. 375.525 4,810.326 ↓ 1.9 422,107 1

Nested Loop Left Join (cost=1.12..227,108.90 rows=220,463 width=56) (actual time=0.137..4,810.326 rows=422,107 loops=1)

15. 404.808 404.808 ↓ 3.5 149,259 1

Index Scan using boxes_fc_country_production_site_production_date_index on boxes b_1 (cost=0.56..5,322.94 rows=42,903 width=48) (actual time=0.102..404.808 rows=149,259 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-27'::date))
  • Filter: (status = ANY ('{placed,complete}'::text[]))
16. 4,029.993 4,029.993 ↑ 45.7 3 149,259

Index Scan using ordered_recipes_shipment_number_ms_recipe_id_index on ordered_recipes r_1 (cost=0.56..3.80 rows=137 width=25) (actual time=0.026..0.027 rows=3 loops=149,259)

  • Index Cond: (b_1.number = shipment_number)
17. 3.730 8.159 ↓ 1.0 13,647 1

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

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

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

19. 1,016.614 10,635.103 ↓ 16.6 149,265 1

Sort (cost=2,720.51..2,743.04 rows=9,010 width=145) (actual time=10,486.081..10,635.103 rows=149,265 loops=1)

  • Sort Key: pd.production_date, b.production_site
  • Sort Method: external merge Disk: 9624kB
20. 113.582 9,618.489 ↓ 16.6 149,265 1

Hash Right Join (cost=0.68..2,128.68 rows=9,010 width=145) (actual time=8,456.222..9,618.489 rows=149,265 loops=1)

  • Hash Cond: (b.production_date = pd.production_date)
21. 9,477.300 9,477.300 ↓ 1.7 149,259 1

CTE Scan on production_site_boxes_with_dishbags b (cost=0.00..1,716.12 rows=85,806 width=145) (actual time=8,428.603..9,477.300 rows=149,259 loops=1)

22. 0.018 27.607 ↓ 3.0 62 1

Hash (cost=0.42..0.42 rows=21 width=4) (actual time=27.607..27.607 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
23. 27.589 27.589 ↓ 3.0 62 1

CTE Scan on production_dates pd (cost=0.00..0.42 rows=21 width=4) (actual time=27.556..27.589 rows=62 loops=1)