explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Ej1

Settings
# exclusive inclusive rows x rows loops node
1. 1.021 4,262.370 ↑ 110.4 1,970 1

GroupAggregate (cost=2,396,783.38..2,462,018.08 rows=217,449 width=178) (actual time=4,261.252..4,262.370 rows=1,970 loops=1)

  • Group Key: ((to_char(sv.line_item_creted_at, 'mm/dd/yyyy'::text))::date), sv.shop_id, sv.product_name, sv.vendor, ca.country, ca.province, sap.title, p.product_type, (rp_order_placed_by(sv.id))
2. 5.889 4,261.349 ↑ 93.4 2,329 1

Sort (cost=2,396,783.38..2,397,327.01 rows=217,449 width=174) (actual time=4,261.241..4,261.349 rows=2,329 loops=1)

  • Sort Key: ((to_char(sv.line_item_creted_at, 'mm/dd/yyyy'::text))::date) DESC, sv.product_name, sv.vendor, ca.country, ca.province, sap.title, p.product_type, (rp_order_placed_by(sv.id))
  • Sort Method: quicksort Memory: 719kB
3. 30.639 4,255.460 ↑ 93.4 2,329 1

Hash Left Join (cost=208,126.95..2,366,888.19 rows=217,449 width=174) (actual time=1,025.220..4,255.460 rows=2,329 loops=1)

  • Hash Cond: (sv.shop_id = si.shop_id)
4. 0.786 4,224.810 ↑ 93.4 2,329 1

Hash Left Join (cost=208,124.64..2,307,902.84 rows=217,449 width=154) (actual time=1,025.093..4,224.810 rows=2,329 loops=1)

  • Hash Cond: ((sv.shop_id = sap.shop_id) AND (sv.app_id = sap.app_id))
5. 0.289 4,224.009 ↑ 93.4 2,329 1

Nested Loop Left Join (cost=208,120.93..2,305,148.40 rows=217,449 width=147) (actual time=1,025.057..4,224.009 rows=2,329 loops=1)

6. 0.691 4,212.075 ↑ 93.4 2,329 1

Nested Loop Left Join (cost=208,120.37..2,096,706.74 rows=217,449 width=134) (actual time=1,025.028..4,212.075 rows=2,329 loops=1)

7. 131.957 4,202.068 ↑ 93.4 2,329 1

Hash Left Join (cost=208,119.81..1,777,938.32 rows=217,449 width=134) (actual time=1,025.011..4,202.068 rows=2,329 loops=1)

  • Hash Cond: (sv.shopify_product_id = p.id)
8. 0.504 3,217.644 ↑ 93.4 2,329 1

Subquery Scan on sv (cost=33,791.11..1,586,226.83 rows=217,449 width=132) (actual time=99.985..3,217.644 rows=2,329 loops=1)

9. 0.272 3,217.140 ↑ 93.4 2,329 1

Append (cost=33,791.11..1,584,052.34 rows=217,449 width=1,259) (actual time=99.984..3,217.140 rows=2,329 loops=1)

10. 0.875 3,216.866 ↑ 93.4 2,329 1

Result (cost=33,791.11..1,582,965.04 rows=217,448 width=1,259) (actual time=99.984..3,216.866 rows=2,329 loops=1)

11. 0.226 3,215.991 ↑ 93.4 2,329 1

Append (cost=33,791.11..1,579,703.32 rows=217,448 width=1,251) (actual time=99.980..3,215.991 rows=2,329 loops=1)

12. 0.865 3,215.762 ↑ 93.4 2,329 1

Result (cost=33,791.11..1,578,616.01 rows=217,446 width=1,251) (actual time=99.979..3,215.762 rows=2,329 loops=1)

13. 0.243 3,214.897 ↑ 93.4 2,329 1

Append (cost=33,791.11..1,575,897.94 rows=217,446 width=1,247) (actual time=99.975..3,214.897 rows=2,329 loops=1)

14. 0.954 3,214.651 ↑ 93.4 2,329 1

Subquery Scan on *SELECT* 1 (cost=33,791.11..1,574,810.69 rows=217,445 width=1,015) (actual time=99.974..3,214.651 rows=2,329 loops=1)

15. 1,606.639 3,213.697 ↑ 93.4 2,329 1

Hash Join (cost=33,791.11..1,572,092.63 rows=217,445 width=1,011) (actual time=99.970..3,213.697 rows=2,329 loops=1)

  • Hash Cond: (l.shopify_order_id = o.id)
16. 1,513.794 1,513.794 ↓ 1.0 1,936,566 1

Index Scan using ix_line_item_created_at_new on shopify_line_items l (cost=0.56..1,406,995.99 rows=1,922,820 width=220) (actual time=0.021..1,513.794 rows=1,936,566 loops=1)

  • Index Cond: (created_at > '2020-05-10 00:00:00'::timestamp without time zone)
17. 50.095 93.264 ↓ 1.1 37,065 1

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

  • Buckets: 8192 Batches: 8 Memory Usage: 2694kB
18. 3.370 43.169 ↓ 1.1 37,065 1

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

19. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: (shop_id = 420)
20. 21.661 21.661 ↑ 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.024..21.661 rows=22,495 loops=1)

  • Index Cond: (shop_id = 420)
21. 2.155 2.155 ↑ 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.155 rows=1,919 loops=1)

  • Index Cond: (shop_id = 420)
22. 2.660 2.660 ↓ 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.017..2.660 rows=2,181 loops=1)

  • Index Cond: (shop_id = 420)
23. 6.152 6.152 ↑ 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.020..6.152 rows=5,026 loops=1)

  • Index Cond: (shop_id = 420)
24. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
25. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: (shop_id = 420)
26. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
27. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
28. 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)
29. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
30. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
31. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Index Cond: (shop_id = 420)
32. 1.696 1.696 ↓ 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.016..1.696 rows=1,413 loops=1)

  • Index Cond: (shop_id = 420)
33. 5.419 5.419 ↓ 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.016..5.419 rows=4,030 loops=1)

  • Index Cond: (shop_id = 420)
34. 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)
35. 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)

36. 0.002 0.002 ↓ 0.0 0 1

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

  • One-Time Filter: false
37. 0.000 0.001 ↓ 0.0 0 1

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

38. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
39. 0.000 0.002 ↓ 0.0 0 1

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

40. 0.002 0.002 ↓ 0.0 0 1

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

  • Group Key: id
41. 0.000 0.000 ↓ 0.0 0 1

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

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

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

43. 0.001 0.001 ↓ 0.0 0 1

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

  • Group Key: id
44. 0.000 0.000 ↓ 0.0 0 1

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

  • One-Time Filter: false
45. 324.965 852.467 ↓ 1.0 1,441,067 1

Hash (cost=148,273.42..148,273.42 rows=1,419,142 width=18) (actual time=852.467..852.467 rows=1,441,067 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2911kB
46. 527.502 527.502 ↓ 1.0 1,441,067 1

Seq Scan on shopify_products p (cost=0.00..148,273.42 rows=1,419,142 width=18) (actual time=0.006..527.502 rows=1,441,067 loops=1)

47. 9.316 9.316 ↑ 1.0 1 2,329

Index Only Scan using shopify_customers_pkey on shopify_customers sc (cost=0.56..1.47 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2,329)

  • Index Cond: (id = sv.shopify_customer_id)
  • Heap Fetches: 2025
48. 11.645 11.645 ↑ 9.0 1 2,329

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.004..0.005 rows=1 loops=2,329)

  • Index Cond: (sc.id = shopify_customer_id)
  • Filter: "default
  • Rows Removed by Filter: 1
49. 0.003 0.015 ↑ 1.0 3 1

Hash (cost=3.66..3.66 rows=3 width=27) (actual time=0.015..0.015 rows=3 loops=1)

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

Index Scan using ix_app_pub_shop_id on shopify_app_publications sap (cost=0.28..3.66 rows=3 width=27) (actual time=0.009..0.012 rows=3 loops=1)

  • Index Cond: (shop_id = 420)
51. 0.001 0.011 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (shop_id = 420)
  • Heap Fetches: 0
Planning time : 8.478 ms
Execution time : 4,263.192 ms