explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4VNl : test

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=137,292,478.89..137,292,478.90 rows=2 width=96) (actual rows= loops=)

  • Sort Key: tmp."Order Date
2. 0.000 0.000 ↓ 0.0

Subquery Scan on tmp (cost=0.00..137,292,478.88 rows=2 width=96) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

MixedAggregate (cost=0.00..137,292,478.86 rows=2 width=136) (actual rows= loops=)

  • Hash Key: to_char(timezone('America/New_York'::text, timezone('utc'::text, sv.created_at)), 'MM-DD-YYYY'::text)
  • Group Key: ()
  • Filter: (CASE WHEN (GROUPING((to_char(timezone('America/New_York'::text, timezone('utc'::text, sv.created_at)), 'MM-DD-YYYY'::text))) = 0) THEN 1 ELSE 0 END = 1)
4. 0.000 0.000 ↓ 0.0

Subquery Scan on sv (cost=0.00..136,050,999.95 rows=99,317,946 width=56) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Append (cost=0.00..134,312,935.89 rows=99,317,946 width=1,223) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Result (cost=0.00..133,679,033.39 rows=99,317,160 width=1,223) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Append (cost=0.00..132,189,275.99 rows=99,317,160 width=1,215) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Result (cost=0.00..131,626,426.84 rows=99,255,399 width=1,215) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Append (cost=0.00..130,385,734.35 rows=99,255,399 width=1,211) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.00..13,887,113.89 rows=9,150,267 width=880) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..13,772,735.55 rows=9,150,267 width=876) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Append (cost=0.00..2,004.28 rows=7,862 width=553) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_orders o (cost=0.00..0.00 rows=1 width=779) (actual rows= loops=)

  • Filter: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone) AND (shop_id = 5))
14. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_orders_2019_06_12 o_1 (cost=0.00..1,948.35 rows=7,859 width=553) (actual rows= loops=)

  • Filter: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone) AND (shop_id = 5))
15. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2018_06_12_created_at on shopify_orders_2018_06_12 o_2 (cost=0.29..8.31 rows=1 width=542) (actual rows= loops=)

  • Index Cond: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)
16. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2017_06_12_created_at on shopify_orders_2017_06_12 o_3 (cost=0.29..8.31 rows=1 width=563) (actual rows= loops=)

  • Index Cond: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)
17. 0.000 0.000 ↓ 0.0

Append (cost=0.00..5.69 rows=8 width=200) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_line_items l (cost=0.00..0.00 rows=1 width=304) (actual rows= loops=)

  • Filter: (o.id = shopify_order_id)
19. 0.000 0.000 ↓ 0.0

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.58 rows=1 width=207) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
20. 0.000 0.000 ↓ 0.0

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.93 rows=2 width=184) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
21. 0.000 0.000 ↓ 0.0

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..1.94 rows=1 width=206) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
22. 0.000 0.000 ↓ 0.0

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..1.10 rows=1 width=204) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
23. 0.000 0.000 ↓ 0.0

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.17 rows=1 width=304) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
24. 0.000 0.000 ↓ 0.0

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.93 rows=1 width=191) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
25. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..116,002,343.47 rows=90,105,132 width=1,169) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..114,876,029.32 rows=90,105,132 width=1,165) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..4,056.71 rows=103,584 width=104) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_refund_line_items rl (cost=0.00..896.29 rows=89 width=36) (actual rows= loops=)

  • Filter: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
29. 0.000 0.000 ↓ 0.0

Append (cost=0.00..35.44 rows=7 width=68) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_line_items l_7 (cost=0.00..0.00 rows=1 width=112) (actual rows= loops=)

  • Filter: (rl.shopify_line_item_id = id)
31. 0.000 0.000 ↓ 0.0

Index Scan using shopify_line_items_2019_01_06_line_items_pk on shopify_line_items_2019_01_06 l_8 (cost=0.29..5.92 rows=1 width=71) (actual rows= loops=)

  • Index Cond: (id = rl.shopify_line_item_id)
32. 0.000 0.000 ↓ 0.0

Index Scan using shopify_line_items_2019_06_12_line_items_pk on shopify_line_items_2019_06_12 l_9 (cost=0.29..6.96 rows=1 width=59) (actual rows= loops=)

  • Index Cond: (id = rl.shopify_line_item_id)
33. 0.000 0.000 ↓ 0.0

Index Scan using shopify_line_items_2018_01_06_line_items_pk on shopify_line_items_2018_01_06 l_10 (cost=0.29..7.77 rows=1 width=72) (actual rows= loops=)

  • Index Cond: (id = rl.shopify_line_item_id)
34. 0.000 0.000 ↓ 0.0

Index Scan using shopify_line_items_2018_06_12_line_items_pk on shopify_line_items_2018_06_12 l_11 (cost=0.29..7.14 rows=1 width=69) (actual rows= loops=)

  • Index Cond: (id = rl.shopify_line_item_id)
35. 0.000 0.000 ↓ 0.0

Index Scan using shopify_line_items_2017_01_06_line_items_pk on shopify_line_items_2017_01_06 l_12 (cost=0.14..0.66 rows=1 width=112) (actual rows= loops=)

  • Index Cond: (id = rl.shopify_line_item_id)
36. 0.000 0.000 ↓ 0.0

Index Scan using shopify_line_items_2017_06_12_line_items_pk on shopify_line_items_2017_06_12 l_13 (cost=0.29..6.96 rows=1 width=62) (actual rows= loops=)

  • Index Cond: (id = rl.shopify_line_item_id)
37. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.99 rows=7 width=31) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_orders o_4 (cost=0.00..0.00 rows=1 width=56) (actual rows= loops=)

  • Filter: ((shop_id = 5) AND (l_7.shopify_order_id = id))
39. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2019_01_06_pkey on shopify_orders_2019_01_06 o_5 (cost=0.29..0.32 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (id = l_7.shopify_order_id)
  • Filter: (shop_id = 5)
40. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2019_06_12_pkey on shopify_orders_2019_06_12 o_6 (cost=0.29..0.34 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (id = l_7.shopify_order_id)
  • Filter: (shop_id = 5)
41. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2018_01_06_pkey on shopify_orders_2018_01_06 o_7 (cost=0.29..0.43 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (id = l_7.shopify_order_id)
  • Filter: (shop_id = 5)
42. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2018_06_12_pkey on shopify_orders_2018_06_12 o_8 (cost=0.29..0.35 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (id = l_7.shopify_order_id)
  • Filter: (shop_id = 5)
43. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2017_01_06_pkey on shopify_orders_2017_01_06 o_9 (cost=0.14..0.16 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (id = l_7.shopify_order_id)
  • Filter: (shop_id = 5)
44. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2017_06_12_pkey on shopify_orders_2017_06_12 o_10 (cost=0.29..0.35 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (id = l_7.shopify_order_id)
  • Filter: (shop_id = 5)
45. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=0.00..66,263.35 rows=61,761 width=1,190) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..65,336.94 rows=61,761 width=1,182) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_order_adjustments a (cost=0.00..761.02 rows=71 width=32) (actual rows= loops=)

  • Filter: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
48. 0.000 0.000 ↓ 0.0

Append (cost=0.00..37.40 rows=7 width=31) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_orders o_11 (cost=0.00..0.00 rows=1 width=56) (actual rows= loops=)

  • Filter: ((shop_id = 5) AND (a.shopify_order_id = id))
50. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2019_01_06_pkey on shopify_orders_2019_01_06 o_12 (cost=0.29..6.56 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (id = a.shopify_order_id)
  • Filter: (shop_id = 5)
51. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2019_06_12_pkey on shopify_orders_2019_06_12 o_13 (cost=0.29..7.07 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (id = a.shopify_order_id)
  • Filter: (shop_id = 5)
52. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2018_01_06_pkey on shopify_orders_2018_01_06 o_14 (cost=0.29..8.03 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (id = a.shopify_order_id)
  • Filter: (shop_id = 5)
53. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2018_06_12_pkey on shopify_orders_2018_06_12 o_15 (cost=0.29..7.46 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (id = a.shopify_order_id)
  • Filter: (shop_id = 5)
54. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2017_01_06_pkey on shopify_orders_2017_01_06 o_16 (cost=0.14..0.78 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (id = a.shopify_order_id)
  • Filter: (shop_id = 5)
55. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2017_06_12_pkey on shopify_orders_2017_06_12 o_17 (cost=0.29..7.46 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (id = a.shopify_order_id)
  • Filter: (shop_id = 5)
56. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=136,505.15..137,312.77 rows=786 width=1,197) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

HashAggregate (cost=136,505.15..137,299.01 rows=786 width=1,185) (actual rows= loops=)

  • Group Key: o_18.id, o_18.created_at, o_18.name, o_18.number, o_18.order_number, o_18.shop_id
58. 0.000 0.000 ↓ 0.0

Merge Join (cost=13,776.49..70,441.66 rows=3,775,057 width=46) (actual rows= loops=)

  • Merge Cond: (o_18.id = sl.shopify_order_id)
59. 0.000 0.000 ↓ 0.0

Sort (cost=2,512.98..2,532.64 rows=7,862 width=38) (actual rows= loops=)

  • Sort Key: o_18.id
60. 0.000 0.000 ↓ 0.0

Append (cost=0.00..2,004.28 rows=7,862 width=38) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_orders o_18 (cost=0.00..0.00 rows=1 width=64) (actual rows= loops=)

  • Filter: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone) AND (shop_id = 5))
62. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_orders_2019_06_12 o_19 (cost=0.00..1,948.35 rows=7,859 width=38) (actual rows= loops=)

  • Filter: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone) AND (shop_id = 5))
63. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2018_06_12_created_at on shopify_orders_2018_06_12 o_20 (cost=0.29..8.31 rows=1 width=38) (actual rows= loops=)

  • Index Cond: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)
64. 0.000 0.000 ↓ 0.0

Index Scan using shopify_orders_2017_06_12_created_at on shopify_orders_2017_06_12 o_21 (cost=0.29..8.31 rows=1 width=37) (actual rows= loops=)

  • Index Cond: ((created_at >= '2019-06-11 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)
65. 0.000 0.000 ↓ 0.0

Sort (cost=11,263.51..11,503.59 rows=96,033 width=16) (actual rows= loops=)

  • Sort Key: sl.shopify_order_id
66. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.89..3,316.18 rows=96,033 width=16) (actual rows= loops=)

  • Hash Cond: (sl.id = s.shopify_shipping_line_id)
67. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_shipping_lines sl (cost=0.00..2,952.33 rows=96,033 width=16) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash (cost=1.84..1.84 rows=84 width=16) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on shopify_shipping_tax_lines s (cost=0.00..1.84 rows=84 width=16) (actual rows= loops=)