explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 93uh : test

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

Sort (cost=122,602,948.48..122,602,948.49 rows=2 width=64) (actual rows= loops=)

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

Subquery Scan on tmp (cost=0.00..122,602,948.47 rows=2 width=64) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

MixedAggregate (cost=0.00..122,602,948.45 rows=2 width=104) (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..121,924,648.10 rows=90,439,436 width=48) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Append (cost=0.00..120,341,957.97 rows=90,439,436 width=1,223) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Result (cost=0.00..119,879,282.77 rows=90,439,236 width=1,223) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Append (cost=0.00..118,522,694.23 rows=90,439,236 width=1,215) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Result (cost=0.00..118,004,234.70 rows=90,377,475 width=1,215) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Append (cost=0.00..116,874,516.26 rows=90,377,475 width=1,211) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.00..420,285.42 rows=272,343 width=880) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..416,881.13 rows=272,343 width=876) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Append (cost=0.00..862.51 rows=234 width=554) (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-09-02 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

Bitmap Heap Scan on shopify_orders_2019_06_12 o_1 (cost=11.80..844.72 rows=231 width=553) (actual rows= loops=)

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

Bitmap Index Scan on shopify_orders_2019_06_12_created_at (cost=0.00..11.74 rows=345 width=0) (actual rows= loops=)

  • Index Cond: ((created_at >= '2019-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
16. 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-09-02 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

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-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)
18. 0.000 0.000 ↓ 0.0

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

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

  • Index Cond: (shopify_order_id = o.id)
21. 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..8.58 rows=2 width=184) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
22. 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..7.03 rows=1 width=206) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
23. 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..6.03 rows=1 width=204) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
24. 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.35 rows=1 width=304) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
25. 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..5.66 rows=1 width=191) (actual rows= loops=)

  • Index Cond: (shopify_order_id = o.id)
26. 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=)

27. 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=)

28. 0.000 0.000 ↓ 0.0

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

29. 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-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
30. 0.000 0.000 ↓ 0.0

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

31. 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)
32. 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)
33. 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)
34. 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)
35. 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)
36. 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)
37. 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)
38. 0.000 0.000 ↓ 0.0

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

39. 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))
40. 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)
41. 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)
42. 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)
43. 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)
44. 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)
45. 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)
46. 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=)

47. 0.000 0.000 ↓ 0.0

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

48. 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-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
49. 0.000 0.000 ↓ 0.0

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

50. 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))
51. 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)
52. 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)
53. 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)
54. 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)
55. 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)
56. 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)
57. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=10,272.52..10,478.02 rows=200 width=1,197) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,272.52..10,474.52 rows=200 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
59. 0.000 0.000 ↓ 0.0

Hash Join (cost=868.32..8,306.24 rows=112,359 width=46) (actual rows= loops=)

  • Hash Cond: (sl.shopify_order_id = o_18.id)
60. 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)
61. 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=)

62. 0.000 0.000 ↓ 0.0

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

63. 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=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=862.51..862.51 rows=234 width=38) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Append (cost=0.00..862.51 rows=234 width=38) (actual rows= loops=)

66. 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-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone) AND (shop_id = 5))
67. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on shopify_orders_2019_06_12 o_19 (cost=11.80..844.72 rows=231 width=38) (actual rows= loops=)

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

Bitmap Index Scan on shopify_orders_2019_06_12_created_at (cost=0.00..11.74 rows=345 width=0) (actual rows= loops=)

  • Index Cond: ((created_at >= '2019-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
69. 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-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)
70. 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-09-02 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Filter: (shop_id = 5)