explain.depesz.com

PostgreSQL's explain analyze made readable

Result: md0J : Sales

Settings
# exclusive inclusive rows x rows loops node
1. 0.197 33,394.105 ↑ 1.0 200 1

Limit (cost=71,097,032.91..71,097,558.63 rows=200 width=204) (actual time=33,393.843..33,394.105 rows=200 loops=1)

  • Buffers: shared hit=5514795, temp read=280 written=281
2. 0.543 33,393.908 ↑ 16,705.3 700 1

Aggregate (cost=71,095,718.61..101,833,776.27 rows=11,693,701 width=204) (actual time=33,393.302..33,393.908 rows=700 loops=1)

  • Buffers: shared hit=5514795, temp read=280 written=281
3. 166.598 33,393.365 ↑ 206,622.3 141 1

Sort (cost=71,095,718.61..71,168,552.98 rows=29,133,747 width=164) (actual time=33,393.288..33,393.365 rows=141 loops=1)

  • Sort Key: sv.email, sv.number, (order_month(sv.id)), (to_char(sv.created_at, 'MM-DD-YYYY'::text)), (order_day(sv.id)), sv.name
  • Sort Method: external merge Disk: 2240kB
  • Buffers: shared hit=5514795, temp read=280 written=281
4. 3,308.693 33,226.767 ↑ 958.6 30,391 1

Subquery Scan on sv (cost=0..60,314,047.78 rows=29,133,747 width=164) (actual time=1.244..33,226.767 rows=30,391 loops=1)

  • Buffers: shared hit=5514795
5. 20.879 29,918.074 ↑ 958.6 30,391 1

Append (cost=0..45,383,002.45 rows=29,133,747 width=1,223) (actual time=1.099..29,918.074 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
6. 35.076 29,897.192 ↑ 958.6 30,391 1

Result (cost=0..45,237,332.68 rows=29,133,746 width=1,223) (actual time=1.098..29,897.192 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
7. 21.523 29,862.116 ↑ 958.6 30,391 1

Append (cost=0..44,800,326.49 rows=29,133,746 width=1,215) (actual time=1.095..29,862.116 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
8. 40.579 29,840.591 ↑ 958.6 30,391 1

Result (cost=0..44,654,657.76 rows=29,133,745 width=1,215) (actual time=1.095..29,840.591 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
9. 22.900 29,800.012 ↑ 958.6 30,391 1

Append (cost=0..44,290,485.95 rows=29,133,745 width=1,211) (actual time=1.092..29,800.012 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
10. 42.624 29,777.110 ↑ 958.6 30,391 1

Subquery Scan on *SELECT* 1 (cost=0..44,144,817.22 rows=29,133,744 width=878) (actual time=1.091..29,777.11 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
11. 28,996.671 29,734.486 ↑ 958.6 30,391 1

Nested Loop (cost=0..43,780,645.42 rows=29,133,744 width=874) (actual time=1.088..29,734.486 rows=30,391 loops=1)

  • Buffers: shared hit=5260217
12. 18.304 89.323 ↑ 1.0 24,942 1

Append (cost=0..3,191.7 rows=25,032 width=552) (actual time=0.011..89.323 rows=24,942 loops=1)

  • Buffers: shared hit=2447
13. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on shopify_orders o (cost=0..0 rows=1 width=779) (actual time=0.002..0.003 rows=0 loops=1)

  • Filter: ((o.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o.created_at <= '2019-09-01 06:18:05.33'::timestamp without time zone) AND (o.shop_id = 5))
14. 25.798 25.798 ↓ 1.0 10,306 1

Seq Scan on shopify_orders_2019_01_06 o_1 (cost=0..1,101.57 rows=10,290 width=550) (actual time=0.007..25.798 rows=10,306 loops=1)

  • Filter: ((o_1.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_1.created_at <= '2019-09-01 06:18:05.33'::timestamp without time zone) AND (o_1.shop_id = 5))
  • Buffers: shared hit=887
15. 45.207 45.207 ↑ 1.0 14,636 1

Seq Scan on shopify_orders_2019_06_12 o_2 (cost=0..1,948.35 rows=14,739 width=553) (actual time=0.008..45.207 rows=14,636 loops=1)

  • Filter: ((o_2.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_2.created_at <= '2019-09-01 06:18:05.33'::timestamp without time zone) AND (o_2.shop_id = 5))
  • Buffers: shared hit=1556
16. 0.006 0.006 ↓ 0.0 0 1

Index Scan using shopify_orders_2018_06_12_created_at on shopify_orders_2018_06_12 o_3 (cost=0.29..8.31 rows=1 width=542) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((o_3.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_3.created_at <= '2019-09-01 06:18:05.33'::timestamp without time zone))
  • Filter: (o_3.shop_id = 5)
  • Buffers: shared hit=2
17. 0.005 0.005 ↓ 0.0 0 1

Index Scan using shopify_orders_2017_06_12_created_at on shopify_orders_2017_06_12 o_4 (cost=0.29..8.31 rows=1 width=563) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: ((o_4.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_4.created_at <= '2019-09-01 06:18:05.33'::timestamp without time zone))
  • Filter: (o_4.shop_id = 5)
  • Buffers: shared hit=2
18. 224.478 648.492 ↑ 8.0 1 24,942

Append (cost=0..2.99 rows=8 width=200) (actual time=0.009..0.026 rows=1 loops=24,942)

  • Buffers: shared hit=300462
19. 0.000 0.000 ↓ 0.0 0 24,942

Seq Scan on shopify_line_items l (cost=0..0 rows=1 width=304) (actual time=0..0 rows=0 loops=24,942)

  • Filter: (o.id = l.shopify_order_id)
20. 99.768 99.768 ↓ 0.0 0 24,942

Index Scan using shopify_line_items_2019_01_06_on_shopify_order_id on shopify_line_items_2019_01_06 l_1 (cost=0.29..0.39 rows=1 width=207) (actual time=0.003..0.004 rows=0 loops=24,942)

  • Index Cond: (l_1.shopify_order_id = o.id)
  • Buffers: shared hit=60620
21. 124.710 124.710 ↑ 2.0 1 24,942

Index Scan using shopify_line_items_2019_06_12_on_shopify_order_id on shopify_line_items_2019_06_12 l_2 (cost=0.29..0.51 rows=2 width=184) (actual time=0.004..0.005 rows=1 loops=24,942)

  • Index Cond: (l_2.shopify_order_id = o.id)
  • Buffers: shared hit=65248
22. 74.826 74.826 ↓ 0.0 0 24,942

Index Scan using shopify_line_items_2018_01_06_on_shopify_order_id on shopify_line_items_2018_01_06 l_3 (cost=0.29..0.82 rows=1 width=206) (actual time=0.003..0.003 rows=0 loops=24,942)

  • Index Cond: (l_3.shopify_order_id = o.id)
  • Buffers: shared hit=49884
23. 49.884 49.884 ↓ 0.0 0 24,942

Index Scan using shopify_line_items_2018_06_12_on_shopify_order_id on shopify_line_items_2018_06_12 l_4 (cost=0.29..0.56 rows=1 width=204) (actual time=0.002..0.002 rows=0 loops=24,942)

  • Index Cond: (l_4.shopify_order_id = o.id)
  • Buffers: shared hit=49884
24. 24.942 24.942 ↓ 0.0 0 24,942

Index Scan using shopify_line_items_2017_01_06_on_shopify_order_id on shopify_line_items_2017_01_06 l_5 (cost=0.14..0.16 rows=1 width=304) (actual time=0.001..0.001 rows=0 loops=24,942)

  • Index Cond: (l_5.shopify_order_id = o.id)
  • Buffers: shared hit=24942
25. 49.884 49.884 ↓ 0.0 0 24,942

Index Scan using shopify_line_items_2017_06_12_on_shopify_order_id on shopify_line_items_2017_06_12 l_6 (cost=0.29..0.5 rows=1 width=191) (actual time=0.002..0.002 rows=0 loops=24,942)

  • Index Cond: (l_6.shopify_order_id = o.id)
  • Buffers: shared hit=49884
26. 0.001 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0..0 rows=1 width=1,169) (actual time=0.002..0.002 rows=0 loops=1)

27. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0..0 rows=0 width=1,165) (actual time=0.001..0.001 rows=0 loops=1)

28. 0.001 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0..0.01 rows=1 width=1,190) (actual time=0.001..0.002 rows=0 loops=1)

29. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0..0 rows=0 width=1,182) (actual time=0..0.001 rows=0 loops=1)

30. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0..1.03 rows=1 width=1,198) (actual time=0.002..0.003 rows=0 loops=1)

31. 0.001 0.002 ↓ 0.0 0 1

Aggregate (cost=0..1.01 rows=1 width=1,186) (actual time=0.002..0.002 rows=0 loops=1)

32. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0..0 rows=0 width=47) (actual time=0..0.001 rows=0 loops=1)