explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u59g

Settings
# exclusive inclusive rows x rows loops node
1. 959.012 20,676.236 ↑ 38.0 90 1

GroupAggregate (cost=246,877.74..247,870.14 rows=3,423 width=141) (actual time=19,604.840..20,676.236 rows=90 loops=1)

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

CTE production_site_boxes_with_dishbags

3. 576.916 18,471.730 ↓ 1.9 147,344 1

GroupAggregate (cost=227,159.35..234,224.71 rows=76,044 width=105) (actual time=17,638.770..18,471.730 rows=147,344 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
4. 3,767.192 17,894.814 ↓ 2.0 416,734 1

Sort (cost=227,159.35..227,668.92 rows=203,827 width=105) (actual time=17,638.725..17,894.814 rows=416,734 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: 49368kB
5. 431.899 14,127.622 ↓ 2.0 416,734 1

Hash Left Join (cost=332.81..208,508.08 rows=203,827 width=105) (actual time=7.599..14,127.622 rows=416,734 loops=1)

  • Hash Cond: (r.ms_recipe_id = recipes.ms_recipe_id)
6. 451.424 13,688.798 ↓ 2.0 416,734 1

Nested Loop Left Join (cost=1.12..203,845.07 rows=203,827 width=56) (actual time=0.653..13,688.798 rows=416,734 loops=1)

7. 8,375.022 8,375.022 ↓ 3.9 147,344 1

Index Scan using boxes_fc_country_production_site_production_date_index on boxes b_1 (cost=0.56..4,780.04 rows=38,022 width=48) (actual time=0.572..8,375.022 rows=147,344 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[]))
8. 4,862.352 4,862.352 ↑ 45.3 3 147,344

Index Scan using ordered_recipes_shipment_number_ms_recipe_id_index on ordered_recipes r (cost=0.56..3.88 rows=136 width=25) (actual time=0.032..0.033 rows=3 loops=147,344)

  • Index Cond: (b_1.number = shipment_number)
9. 3.256 6.925 ↓ 1.0 13,647 1

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

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

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

11. 1,011.756 19,717.224 ↓ 4.3 147,344 1

Sort (cost=12,653.03..12,738.58 rows=34,220 width=141) (actual time=19,575.837..19,717.224 rows=147,344 loops=1)

  • Sort Key: b.production_date, b.production_site
  • Sort Method: external merge Disk: 8696kB
12. 18,705.468 18,705.468 ↓ 4.3 147,344 1

CTE Scan on production_site_boxes_with_dishbags b (cost=0.00..10,075.83 rows=34,220 width=141) (actual time=17,638.778..18,705.468 rows=147,344 loops=1)

  • Filter: (production_date = ANY ('{2018-12-29,2018-12-30,2018-12-30,2018-12-30,2018-12-31,2019-01-01,2019-01-02,2019-01-02,2019-01-03,2019-01-03,2019-01-03,2019-01-04,2019-01-05,2019-01-06,2019-01-06,2019-01-06,2019-01-07,2019-01-07,2019-01-07,2019-01-08,2019-01-08,2019-01-08,2019-01-09,2019-01-09,2019-01-09,2019-01-10,2019-01-10,2019-01-11,2019-01-11,2019-01-12,2019-01-13,2019-01-13,2019-01-13,2019-01-14,2019-01-14,2019-01-14,2019-01-15,2019-01-15,2019-01-15,2019-01-16,2019-01-16,2019-01-16,2019-01-17,2019-01-17,2019-01-18,2019-01-18,2019-01-19,2019-01-20,2019-01-20,2019-01-20,2019-01-21,2019-01-21,2019-01-21,2019-01-22,2019-01-22,2019-01-22,2019-01-23,2019-01-23,2019-01-23,2019-01-24,2019-01-24,2019-01-25,2019-01-25,2019-01-26,2019-01-27,2019-01-27,2019-01-27,2019-01-28,2019-01-28,2019-01-28,2019-01-29,2019-01-29,2019-01-29,2019-01-30,2019-01-30,2019-01-30,2019-01-31,2019-01-31,2019-02-01,2019-02-01,2019-02-02,2019-02-03,2019-02-03,2019-02-03,2019-02-04,2019-02-04,2019-02-04,2019-02-05,2019-02-05,2019-02-05}'::date[]))