explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZoWr

Settings
# exclusive inclusive rows x rows loops node
1. 218.818 11,801.949 ↑ 32.8 128 1

GroupAggregate (cost=263,662.87..263,973.64 rows=4,200 width=145) (actual time=11,415.114..11,801.949 rows=128 loops=1)

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

CTE production_dates

3. 2.188 45.262 ↓ 3.0 62 1

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

  • Group Key: pd_1.production_date
4. 8.870 43.074 ↓ 1.8 4,304 1

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

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

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

6. 2.868 2.868 ↑ 1.0 116 1

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

  • Filter: ((production_date >= '2018-12-29'::date) AND (fc_country = 'us'::text))
  • Rows Removed by Filter: 3910
7. 20.068 20.068 ↓ 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.013..0.173 rows=162 loops=116)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 90kB
9. 2.672 2.672 ↑ 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.043..2.672 rows=2,554 loops=1)

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

CTE production_site_boxes_with_dishbags

11. 728.400 10,101.759 ↓ 1.7 149,264 1

GroupAggregate (cost=252,423.70..260,110.14 rows=85,806 width=105) (actual time=9,068.656..10,101.759 rows=149,264 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,846.282 9,373.359 ↓ 1.9 422,121 1

Sort (cost=252,423.70..252,974.86 rows=220,462 width=105) (actual time=9,068.614..9,373.359 rows=422,121 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: 50120kB
13. 386.039 5,527.077 ↓ 1.9 422,121 1

Hash Left Join (cost=332.81..232,125.31 rows=220,462 width=105) (actual time=7.403..5,527.077 rows=422,121 loops=1)

  • Hash Cond: (r_1.ms_recipe_id = recipes.ms_recipe_id)
14. 368.216 5,133.857 ↓ 1.9 422,121 1

Nested Loop Left Join (cost=1.12..227,108.80 rows=220,462 width=56) (actual time=0.201..5,133.857 rows=422,121 loops=1)

15. 436.985 436.985 ↓ 3.5 149,264 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.133..436.985 rows=149,264 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,328.656 4,328.656 ↑ 45.7 3 149,264

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.027..0.029 rows=3 loops=149,264)

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

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

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

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

19. 1,148.233 11,583.131 ↓ 16.6 149,270 1

Sort (cost=2,720.51..2,743.04 rows=9,010 width=145) (actual time=11,408.422..11,583.131 rows=149,270 loops=1)

  • Sort Key: pd.production_date, b.production_site
  • Sort Method: external merge Disk: 9624kB
20. 10,434.898 10,434.898 ↓ 16.6 149,270 1

Hash Right Join (cost=0.68..2,128.68 rows=9,010 width=145) (actual time=9,114.027..10,434.898 rows=149,270 loops=1)

  • Hash Cond: (b.production_date = pd.production_date)