explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n2vn

Settings
# exclusive inclusive rows x rows loops node
1. 190.563 1,324,512.742 ↑ 1.0 1 1

Aggregate (cost=60,004,598.50..60,004,598.51 rows=1 width=8) (actual time=1,324,512.742..1,324,512.742 rows=1 loops=1)

2. 994,141.703 1,324,322.179 ↑ 30.8 203,728 1

Merge Right Join (cost=1,003,188.51..59,926,250.99 rows=6,267,801 width=1,829) (actual time=114,952.163..1,324,322.179 rows=203,728 loops=1)

  • Merge Cond: (r.shopify_order_id = "*SELECT* 1".id)
3. 12,277.802 12,277.802 ↑ 1.0 848,630 1

Index Only Scan using index_shopify_refunds_on_shopify_order_id on shopify_refunds r (cost=0.42..129,837.25 rows=853,181 width=8) (actual time=0.023..12,277.802 rows=848,630 loops=1)

  • Heap Fetches: 106727
4. 296.217 317,902.674 ↑ 27.0 203,728 1

Materialize (cost=1,003,188.09..1,392,129.91 rows=5,501,778 width=40) (actual time=111,113.245..317,902.674 rows=203,728 loops=1)

5. 2,201.757 317,606.457 ↑ 27.6 199,435 1

Merge Left Join (cost=1,003,188.09..1,378,375.47 rows=5,501,778 width=40) (actual time=111,113.241..317,606.457 rows=199,435 loops=1)

  • Merge Cond: ("*SELECT* 1".id = t.shopify_order_id)
6. 343.063 65,996.387 ↑ 2.4 109,148 1

Sort (cost=1,003,187.65..1,003,853.77 rows=266,446 width=40) (actual time=65,798.715..65,996.387 rows=109,148 loops=1)

  • Sort Key: "*SELECT* 1".id
  • Sort Method: external merge Disk: 2784kB
7. 117.157 65,653.324 ↑ 2.4 109,148 1

Nested Loop Left Join (cost=1.30..975,012.23 rows=266,446 width=40) (actual time=7.052..65,653.324 rows=109,148 loops=1)

8. 102.336 64,993.727 ↑ 1.9 108,488 1

Nested Loop Left Join (cost=0.87..700,206.65 rows=201,325 width=40) (actual time=7.031..64,993.727 rows=108,488 loops=1)

9. 17.437 36,878.469 ↑ 1.4 108,158 1

Append (cost=0.00..218,956.06 rows=152,120 width=951) (actual time=6.982..36,878.469 rows=108,158 loops=1)

10. 43.708 24,985.520 ↓ 1.1 92,315 1

Subquery Scan on *SELECT* 1 (cost=0.00..76,383.46 rows=86,489 width=845) (actual time=6.981..24,985.520 rows=92,315 loops=1)

11. 51.886 24,941.812 ↓ 1.1 92,315 1

Result (cost=0.00..75,302.35 rows=86,489 width=841) (actual time=6.978..24,941.812 rows=92,315 loops=1)

12. 18.625 24,889.926 ↓ 1.1 92,315 1

Append (cost=0.00..74,437.46 rows=86,489 width=769) (actual time=6.974..24,889.926 rows=92,315 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((test IS NOT TRUE) AND (created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone) AND (shop_id = 873))
14. 585.664 585.664 ↓ 1.2 7,445 1

Index Scan using shopify_orders_2019_03_06_ix_shop_id_created_at on shopify_orders_2019_03_06 o_1 (cost=0.42..5,756.09 rows=6,195 width=709) (actual time=6.971..585.664 rows=7,445 loops=1)

  • Index Cond: ((shop_id = 873) AND (created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone))
  • Filter: (test IS NOT TRUE)
15. 2,960.710 2,960.710 ↓ 1.1 19,972 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_2 (cost=0.43..13,573.13 rows=18,612 width=794) (actual time=11.978..2,960.710 rows=19,972 loops=1)

  • Index Cond: (shop_id = 873)
  • Filter: ((test IS NOT TRUE) AND (created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone))
16. 8,961.468 8,961.468 ↓ 1.0 35,717 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_3 (cost=0.43..31,083.36 rows=35,373 width=721) (actual time=8.247..8,961.468 rows=35,717 loops=1)

  • Index Cond: (shop_id = 873)
  • Filter: ((test IS NOT TRUE) AND (created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone))
17. 8,552.747 8,552.747 ↓ 1.2 21,950 1

Index Scan using shopify_orders_2020_01_03_shop_id on shopify_orders_2020_01_03 o_4 (cost=0.43..16,394.28 rows=19,050 width=884) (actual time=8.017..8,552.747 rows=21,950 loops=1)

  • Index Cond: (shop_id = 873)
  • Filter: ((test IS NOT TRUE) AND (created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 3
18. 3,810.710 3,810.710 ↑ 1.0 7,231 1

Index Scan using shopify_orders_2020_03_06_shop_id on shopify_orders_2020_03_06 o_5 (cost=0.42..7,198.15 rows=7,258 width=692) (actual time=8.020..3,810.710 rows=7,231 loops=1)

  • Index Cond: (shop_id = 873)
  • Filter: ((test IS NOT TRUE) AND (created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 3
19. 13.327 11,875.512 ↑ 4.1 15,843 1

Subquery Scan on *SELECT* 2 (cost=81,550.50..141,812.00 rows=65,631 width=951) (actual time=2,911.526..11,875.512 rows=15,843 loops=1)

20. 8,468.364 11,862.185 ↑ 4.1 15,843 1

Hash Join (cost=81,550.50..140,171.23 rows=65,631 width=927) (actual time=2,911.518..11,862.185 rows=15,843 loops=1)

  • Hash Cond: (r_1.shopify_order_id = o_6.id)
21. 566.315 566.315 ↑ 1.0 523,663 1

Seq Scan on shopify_refunds r_1 (cost=0.00..32,477.72 rows=524,214 width=24) (actual time=4.130..566.315 rows=523,663 loops=1)

  • Filter: ((created_at >= '2019-04-01 04:00:00'::timestamp without time zone) AND (created_at <= '2020-03-20 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 325006
22. 88.026 2,827.506 ↓ 1.1 98,951 1

Hash (cost=79,737.47..79,737.47 rows=93,762 width=32) (actual time=2,827.505..2,827.506 rows=98,951 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3991kB
23. 10.839 2,739.480 ↓ 1.1 98,951 1

Append (cost=0.00..79,737.47 rows=93,762 width=32) (actual time=8.280..2,739.480 rows=98,951 loops=1)

24. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on shopify_orders o_6 (cost=0.00..0.00 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (shop_id = 873)
25. 1,128.299 1,128.299 ↑ 1.2 2,626 1

Index Scan using shopify_orders_2019_01_03_on_shop_id on shopify_orders_2019_01_03 o_7 (cost=0.42..2,577.81 rows=3,063 width=32) (actual time=8.275..1,128.299 rows=2,626 loops=1)

  • Index Cond: (shop_id = 873)
26. 927.368 927.368 ↓ 1.1 9,892 1

Index Scan using shopify_orders_2019_03_06_on_shop_id on shopify_orders_2019_03_06 o_8 (cost=0.42..7,620.51 rows=9,021 width=32) (actual time=13.799..927.368 rows=9,892 loops=1)

  • Index Cond: (shop_id = 873)
27. 44.582 44.582 ↓ 1.1 19,972 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_9 (cost=0.43..13,480.07 rows=18,613 width=33) (actual time=0.039..44.582 rows=19,972 loops=1)

  • Index Cond: (shop_id = 873)
28. 87.876 87.876 ↓ 1.0 35,717 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_10 (cost=0.43..30,906.49 rows=35,375 width=32) (actual time=0.036..87.876 rows=35,717 loops=1)

  • Index Cond: (shop_id = 873)
29. 45.067 45.067 ↓ 2.6 323 1

Index Scan using shopify_orders_2018_01_06_on_shop_id on shopify_orders_2018_01_06 o_11 (cost=0.42..114.35 rows=122 width=31) (actual time=4.267..45.067 rows=323 loops=1)

  • Index Cond: (shop_id = 873)
30. 375.312 375.312 ↓ 1.3 1,234 1

Index Scan using shopify_orders_2018_06_12_on_shop_id on shopify_orders_2018_06_12 o_12 (cost=0.43..847.18 rows=985 width=32) (actual time=7.139..375.312 rows=1,234 loops=1)

  • Index Cond: (shop_id = 873)
31. 6.913 6.913 ↓ 0.0 0 1

Index Scan using shopify_orders_2017_01_06_on_shop_id on shopify_orders_2017_01_06 o_13 (cost=0.42..53.20 rows=51 width=31) (actual time=6.913..6.913 rows=0 loops=1)

  • Index Cond: (shop_id = 873)
32. 8.000 8.000 ↓ 0.0 0 1

Index Scan using shopify_orders_2017_06_12_on_shop_id on shopify_orders_2017_06_12 o_14 (cost=0.42..152.38 rows=151 width=31) (actual time=8.000..8.000 rows=0 loops=1)

  • Index Cond: (shop_id = 873)
33. 7.999 7.999 ↓ 0.0 0 1

Index Scan using shopify_orders_2016_01_12_on_shop_id on shopify_orders_2016_01_12 o_15 (cost=0.42..41.74 rows=51 width=31) (actual time=7.999..7.999 rows=0 loops=1)

  • Index Cond: (shop_id = 873)
34. 3.997 3.997 ↓ 0.0 0 1

Index Scan using shopify_orders_2015_01_12_on_shop_id on shopify_orders_2015_01_12 o_16 (cost=0.42..9.37 rows=8 width=31) (actual time=3.997..3.997 rows=0 loops=1)

  • Index Cond: (shop_id = 873)
35. 4.003 4.003 ↓ 0.0 0 1

Index Scan using shopify_orders_2014_01_12_ix_shop_id_created_at on shopify_orders_2014_01_12 o_17 (cost=0.29..2.31 rows=1 width=32) (actual time=4.003..4.003 rows=0 loops=1)

  • Index Cond: (shop_id = 873)
36. 3.747 3.747 ↓ 0.0 0 1

Index Scan using shopify_orders_2013_ix_shop_id_created_at on shopify_orders_2013 o_18 (cost=0.41..2.43 rows=1 width=32) (actual time=3.747..3.747 rows=0 loops=1)

  • Index Cond: (shop_id = 873)
37. 69.926 69.926 ↓ 1.2 21,953 1

Index Scan using shopify_orders_2020_01_03_shop_id on shopify_orders_2020_01_03 o_19 (cost=0.43..16,299.01 rows=19,054 width=32) (actual time=0.027..69.926 rows=21,953 loops=1)

  • Index Cond: (shop_id = 873)
38. 15.549 15.549 ↑ 1.0 7,234 1

Index Scan using shopify_orders_2020_03_06_shop_id on shopify_orders_2020_03_06 o_20 (cost=0.42..7,161.82 rows=7,265 width=32) (actual time=0.023..15.549 rows=7,234 loops=1)

  • Index Cond: (shop_id = 873)
39. 148.703 28,012.922 ↑ 1.0 1 108,158

Nested Loop Left Join (cost=0.87..3.14 rows=1 width=8) (actual time=0.257..0.259 rows=1 loops=108,158)

40. 24,011.076 24,011.076 ↑ 1.0 1 108,158

Index Scan using index_shopify_fulfillments_on_shopify_order_id on shopify_fulfillments sf (cost=0.43..1.84 rows=1 width=16) (actual time=0.222..0.222 rows=1 loops=108,158)

  • Index Cond: ("*SELECT* 1".id = shopify_order_id)
41. 3,853.143 3,853.143 ↑ 1.0 1 104,139

Index Only Scan using index_shopify_fulfillment_trackings_on_shopify_fulfillment_id on shopify_fulfillment_trackings sft (cost=0.43..1.29 rows=1 width=8) (actual time=0.037..0.037 rows=1 loops=104,139)

  • Index Cond: (shopify_fulfillment_id = sf.id)
  • Heap Fetches: 104139
42. 542.440 542.440 ↑ 1.0 1 108,488

Index Only Scan using index_shopify_fulfillments_on_shopify_order_id on shopify_fulfillments f (cost=0.43..1.35 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=108,488)

  • Index Cond: (shopify_order_id = "*SELECT* 1".id)
  • Heap Fetches: 10371
43. 249,408.313 249,408.313 ↓ 1.0 13,958,981 1

Index Only Scan using index_shopify_transactions_on_shopify_order_id on shopify_transactions t (cost=0.43..284,375.91 rows=13,784,758 width=8) (actual time=5.018..249,408.313 rows=13,958,981 loops=1)

  • Heap Fetches: 499582
Planning time : 295.427 ms
Execution time : 1,324,518.550 ms