explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sr8b

Settings
# exclusive inclusive rows x rows loops node
1. 0.186 420.277 ↑ 83.0 356 1

GroupAggregate (cost=455,622.72..464,489.22 rows=29,555 width=178) (actual time=420.083..420.277 rows=356 loops=1)

  • Group Key: ((to_char("*SELECT* 1".line_item_creted_at, 'mm/dd/yyyy'::text))::date), "*SELECT* 1".shop_id, "*SELECT* 1".product_name, "*SELECT* 1".vendor, ca.country, ca.province, sap.title, p.product_type, (rp_order_placed_by("*SELECT* 1".id))
2. 1.151 420.091 ↑ 69.2 427 1

Sort (cost=455,622.72..455,696.61 rows=29,555 width=174) (actual time=420.073..420.091 rows=427 loops=1)

  • Sort Key: ((to_char("*SELECT* 1".line_item_creted_at, 'mm/dd/yyyy'::text))::date) DESC, "*SELECT* 1".product_name, "*SELECT* 1".vendor, ca.country, ca.province, sap.title, p.product_type, (rp_order_placed_by("*SELECT* 1".id))
  • Sort Method: quicksort Memory: 138kB
3. 6.889 418.940 ↑ 69.2 427 1

Hash Left Join (cost=33,844.62..451,984.10 rows=29,555 width=174) (actual time=127.048..418.940 rows=427 loops=1)

  • Hash Cond: ("*SELECT* 1".shop_id = si.shop_id)
4. 0.196 412.037 ↑ 69.2 427 1

Hash Left Join (cost=33,842.31..443,965.00 rows=29,555 width=154) (actual time=126.942..412.037 rows=427 loops=1)

  • Hash Cond: (("*SELECT* 1".shop_id = sap.shop_id) AND ("*SELECT* 1".app_id = sap.app_id))
5. 0.244 411.642 ↑ 69.2 427 1

Nested Loop Left Join (cost=33,792.66..443,541.48 rows=29,555 width=147) (actual time=126.725..411.642 rows=427 loops=1)

6. 0.400 410.117 ↑ 69.2 427 1

Nested Loop Left Join (cost=33,792.10..415,210.74 rows=29,555 width=134) (actual time=126.713..410.117 rows=427 loops=1)

7. 0.551 408.863 ↑ 69.2 427 1

Nested Loop Left Join (cost=33,791.54..359,584.84 rows=29,555 width=134) (actual time=126.701..408.863 rows=427 loops=1)

8. 0.057 407.458 ↑ 69.2 427 1

Append (cost=33,791.11..309,593.31 rows=29,555 width=1,259) (actual time=126.687..407.458 rows=427 loops=1)

9. 0.175 407.397 ↑ 69.2 427 1

Result (cost=33,791.11..309,445.48 rows=29,554 width=1,259) (actual time=126.687..407.397 rows=427 loops=1)

10. 0.049 407.222 ↑ 69.2 427 1

Append (cost=33,791.11..309,002.17 rows=29,554 width=1,251) (actual time=126.683..407.222 rows=427 loops=1)

11. 0.167 407.166 ↑ 69.2 427 1

Result (cost=33,791.11..308,854.34 rows=29,552 width=1,251) (actual time=126.682..407.166 rows=427 loops=1)

12. 0.053 406.999 ↑ 69.2 427 1

Append (cost=33,791.11..308,484.94 rows=29,552 width=1,247) (actual time=126.678..406.999 rows=427 loops=1)

13. 0.195 406.943 ↑ 69.2 427 1

Subquery Scan on *SELECT* 1 (cost=33,791.11..308,337.16 rows=29,551 width=1,015) (actual time=126.678..406.943 rows=427 loops=1)

14. 163.655 406.748 ↑ 69.2 427 1

Hash Join (cost=33,791.11..307,967.77 rows=29,551 width=1,011) (actual time=126.674..406.748 rows=427 loops=1)

  • Hash Cond: (l.shopify_order_id = o.id)
15. 158.422 158.422 ↑ 1.4 186,340 1

Index Scan using ix_line_item_created_at_new on shopify_line_items l (cost=0.56..253,929.38 rows=261,317 width=220) (actual time=0.014..158.422 rows=186,340 loops=1)

  • Index Cond: (created_at > '2020-05-20 00:00:00'::timestamp without time zone)
16. 47.154 84.671 ↓ 1.1 37,065 1

Hash (cost=30,590.79..30,590.79 rows=33,501 width=656) (actual time=84.671..84.671 rows=37,065 loops=1)

  • Buckets: 8192 Batches: 8 Memory Usage: 2694kB
17. 2.742 37.517 ↓ 1.1 37,065 1

Append (cost=0.00..30,590.79 rows=33,501 width=656) (actual time=0.017..37.517 rows=37,065 loops=1)

18. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on shopify_orders o (cost=0.00..0.00 rows=1 width=644) (actual time=0.003..0.004 rows=0 loops=1)

  • Filter: (shop_id = 420)
19. 16.010 16.010 ↑ 1.0 22,495 1

Index Scan using shopify_orders_2019_01_03_on_shop_id on shopify_orders_2019_01_03 o_1 (cost=0.42..20,025.95 rows=22,705 width=663) (actual time=0.013..16.010 rows=22,495 loops=1)

  • Index Cond: (shop_id = 420)
20. 2.249 2.249 ↑ 1.0 1,919 1

Index Scan using shopify_orders_2019_03_06_on_shop_id on shopify_orders_2019_03_06 o_2 (cost=0.43..1,775.70 rows=1,943 width=666) (actual time=0.021..2.249 rows=1,919 loops=1)

  • Index Cond: (shop_id = 420)
21. 2.746 2.746 ↓ 2.9 2,181 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_3 (cost=0.43..750.22 rows=756 width=742) (actual time=0.019..2.746 rows=2,181 loops=1)

  • Index Cond: (shop_id = 420)
22. 6.480 6.480 ↑ 1.1 5,026 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_4 (cost=0.43..5,155.65 rows=5,316 width=610) (actual time=0.041..6.480 rows=5,026 loops=1)

  • Index Cond: (shop_id = 420)
23. 0.011 0.011 ↓ 0.0 0 1

Index Scan using shopify_orders_2018_01_06_on_shop_id on shopify_orders_2018_01_06 o_5 (cost=0.42..112.46 rows=113 width=655) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
24. 0.009 0.009 ↑ 439.0 1 1

Index Scan using shopify_orders_2018_06_12_on_shop_id on shopify_orders_2018_06_12 o_6 (cost=0.43..445.99 rows=439 width=580) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (shop_id = 420)
25. 0.007 0.007 ↓ 0.0 0 1

Index Scan using shopify_orders_2017_01_06_on_shop_id on shopify_orders_2017_01_06 o_7 (cost=0.42..41.05 rows=39 width=640) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
26. 0.006 0.006 ↓ 0.0 0 1

Index Scan using shopify_orders_2017_06_12_on_shop_id on shopify_orders_2017_06_12 o_8 (cost=0.42..128.58 rows=126 width=645) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
27. 0.005 0.005 ↓ 0.0 0 1

Index Scan using shopify_orders_2016_01_12_on_shop_id on shopify_orders_2016_01_12 o_9 (cost=0.42..41.11 rows=40 width=579) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
28. 0.007 0.007 ↓ 0.0 0 1

Index Scan using shopify_orders_2015_01_12_on_shop_id on shopify_orders_2015_01_12 o_10 (cost=0.42..6.31 rows=5 width=598) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
29. 0.005 0.005 ↓ 0.0 0 1

Index Scan using shopify_orders_2014_01_12_on_shop_id on shopify_orders_2014_01_12 o_11 (cost=0.41..5.48 rows=4 width=587) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
30. 0.007 0.007 ↓ 0.0 0 1

Index Scan using shopify_orders_2013_ix_shop_id_created_at on shopify_orders_2013 o_12 (cost=0.41..2.43 rows=1 width=598) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
31. 1.859 1.859 ↓ 2.2 1,413 1

Index Scan using shopify_orders_2020_01_03_shop_id on shopify_orders_2020_01_03 o_13 (cost=0.43..626.81 rows=653 width=794) (actual time=0.019..1.859 rows=1,413 loops=1)

  • Index Cond: (shop_id = 420)
32. 5.364 5.364 ↓ 3.0 4,030 1

Index Scan using shopify_orders_2020_03_06_shop_id on shopify_orders_2020_03_06 o_14 (cost=0.43..1,303.37 rows=1,359 width=612) (actual time=0.019..5.364 rows=4,030 loops=1)

  • Index Cond: (shop_id = 420)
33. 0.006 0.006 ↓ 0.0 0 1

Index Scan using shopify_orders_shopify_orders_2020_06_09_shop_id on shopify_orders_2020_06_09 o_15 (cost=0.14..2.16 rows=1 width=779) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
34. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=1,070) (actual time=0.003..0.003 rows=0 loops=1)

35. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,042) (actual time=0.001..0.002 rows=0 loops=1)

  • One-Time Filter: false
36. 0.001 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.02 rows=1 width=1,157) (actual time=0.002..0.002 rows=0 loops=1)

37. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
38. 0.001 0.005 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..0.05 rows=1 width=1,157) (actual time=0.005..0.005 rows=0 loops=1)

39. 0.003 0.004 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,117) (actual time=0.004..0.004 rows=0 loops=1)

  • Group Key: id
40. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
41. 0.001 0.004 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=0.00..0.05 rows=1 width=1,164) (actual time=0.004..0.004 rows=0 loops=1)

42. 0.002 0.003 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,112) (actual time=0.003..0.003 rows=0 loops=1)

  • Group Key: id
43. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
44. 0.854 0.854 ↑ 1.0 1 427

Index Scan using shopify_products_pkey on shopify_products p (cost=0.43..1.68 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=427)

  • Index Cond: ("*SELECT* 1".shopify_product_id = id)
45. 0.854 0.854 ↑ 1.0 1 427

Index Only Scan using shopify_customers_pkey on shopify_customers sc (cost=0.56..1.88 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=427)

  • Index Cond: (id = "*SELECT* 1".shopify_customer_id)
  • Heap Fetches: 427
46. 1.281 1.281 ↑ 9.0 1 427

Index Scan using index_shopify_customer_addresses_on_shopify_customer_id on shopify_customer_addresses ca (cost=0.56..0.87 rows=9 width=29) (actual time=0.003..0.003 rows=1 loops=427)

  • Index Cond: (sc.id = shopify_customer_id)
  • Filter: "default
  • Rows Removed by Filter: 1
47. 0.005 0.199 ↑ 1.0 3 1

Hash (cost=49.60..49.60 rows=3 width=27) (actual time=0.198..0.199 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.194 0.194 ↑ 1.0 3 1

Seq Scan on shopify_app_publications sap (cost=0.00..49.60 rows=3 width=27) (actual time=0.123..0.194 rows=3 loops=1)

  • Filter: (shop_id = 420)
  • Rows Removed by Filter: 2142
49. 0.003 0.014 ↑ 1.0 1 1

Hash (cost=2.29..2.29 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.011 0.011 ↑ 1.0 1 1

Index Only Scan using index_shop_infos_on_shop_id on shop_infos si (cost=0.28..2.29 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (shop_id = 420)
  • Heap Fetches: 0
Planning time : 8.166 ms
Execution time : 420.972 ms