explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tO6w : TEst

Settings
# exclusive inclusive rows x rows loops node
1. 0.077 17,252.871 ↓ 2.5 500 1

Limit (cost=2,355,548.89..2,355,549.39 rows=200 width=292) (actual time=17,252.757..17,252.871 rows=500 loops=1)

2. 53.091 17,252.794 ↓ 2.5 500 1

Sort (cost=2,355,548.89..2,355,549.39 rows=200 width=292) (actual time=17,252.756..17,252.794 rows=500 loops=1)

  • Sort Key: tmp."Order ID", tmp.order_discr
  • Sort Method: top-N heapsort Memory: 95kB
3. 11.311 17,199.703 ↓ 253.6 50,718 1

Subquery Scan on tmp (cost=1,532,644.88..2,355,541.25 rows=200 width=292) (actual time=11,679.650..17,199.703 rows=50,718 loops=1)

4. 5,464.934 17,188.392 ↓ 253.6 50,718 1

GroupAggregate (cost=1,532,644.88..2,355,539.25 rows=200 width=304) (actual time=11,679.648..17,188.392 rows=50,718 loops=1)

  • Group Key: sv.id, sv.name
  • Filter: (CASE WHEN ((GROUPING(sv.id) = 0) AND (GROUPING(sv.name) = 0)) THEN 1 ELSE 0 END = 1)
5. 131.661 11,723.458 ↑ 14.3 102,059 1

Sort (cost=1,532,644.88..1,536,295.95 rows=1,460,430 width=92) (actual time=11,676.974..11,723.458 rows=102,059 loops=1)

  • Sort Key: sv.id, sv.name
  • Sort Method: external merge Disk: 8,216kB
6. 18.887 11,591.797 ↑ 14.3 102,059 1

Subquery Scan on sv (cost=0.43..1,297,535.73 rows=1,460,430 width=92) (actual time=1.091..11,591.797 rows=102,059 loops=1)

7. 12.702 11,572.910 ↑ 14.3 102,059 1

Append (cost=0.43..1,282,931.43 rows=1,460,430 width=1,223) (actual time=1.090..11,572.910 rows=102,059 loops=1)

8. 21.770 9,602.001 ↑ 28.3 51,615 1

Result (cost=0.43..1,122,243.72 rows=1,460,230 width=1,223) (actual time=1.089..9,602.001 rows=51,615 loops=1)

9. 5.928 9,580.231 ↑ 28.3 51,615 1

Append (cost=0.43..1,100,340.27 rows=1,460,230 width=1,215) (actual time=1.085..9,580.231 rows=51,615 loops=1)

10. 21.118 9,160.132 ↑ 26.5 51,601 1

Result (cost=0.43..1,002,987.28 rows=1,367,695 width=1,215) (actual time=1.084..9,160.132 rows=51,601 loops=1)

11. 5.887 9,139.014 ↑ 26.5 51,601 1

Append (cost=0.43..985,891.09 rows=1,367,695 width=1,211) (actual time=1.080..9,139.014 rows=51,601 loops=1)

12. 24.309 2,804.406 ↑ 26.2 51,468 1

Subquery Scan on *SELECT* 1 (cost=0.43..809,680.80 rows=1,349,857 width=1,059) (actual time=1.079..2,804.406 rows=51,468 loops=1)

13. 71.977 2,780.097 ↑ 26.2 51,468 1

Nested Loop (cost=0.43..792,807.59 rows=1,349,857 width=1,055) (actual time=1.075..2,780.097 rows=51,468 loops=1)

14. 6.048 2,505.280 ↑ 1.1 50,710 1

Append (cost=0.00..35,329.36 rows=57,278 width=724) (actual time=0.705..2,505.280 rows=50,710 loops=1)

15. 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.003..0.003 rows=0 loops=1)

  • Filter: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone) AND (shop_id = 80))
16. 2,104.425 2,104.425 ↑ 1.2 32,764 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_1 (cost=0.42..26,113.31 rows=38,559 width=779) (actual time=0.702..2,104.425 rows=32,764 loops=1)

  • Index Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 12,236
17. 394.804 394.804 ↑ 1.0 17,946 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_2 (cost=0.42..8,929.66 rows=18,718 width=610) (actual time=0.358..394.804 rows=17,946 loops=1)

  • Index Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
18. 202.840 202.840 ↑ 24.0 1 50,710

Index Scan using index_shopify_line_items_on_shopify_order_id on shopify_line_items l (cost=0.43..12.93 rows=24 width=255) (actual time=0.004..0.004 rows=1 loops=50,710)

  • Index Cond: (shopify_order_id = o.id)
19. 0.117 6,328.721 ↑ 134.1 133 1

Subquery Scan on *SELECT* 2 (cost=82,104.22..169,371.81 rows=17,838 width=1,137) (actual time=3,702.436..6,328.721 rows=133 loops=1)

20. 78.514 6,328.604 ↑ 134.1 133 1

Hash Join (cost=82,104.22..169,104.24 rows=17,838 width=1,129) (actual time=3,702.429..6,328.604 rows=133 loops=1)

  • Hash Cond: (o_3.id = l_1.shopify_order_id)
21. 15.117 3,377.763 ↑ 1.0 139,332 1

Append (cost=0.00..82,071.95 rows=142,144 width=32) (actual time=0.647..3,377.763 rows=139,332 loops=1)

22. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: (shop_id = 80)
23. 421.546 421.546 ↓ 1.0 12,795 1

Index Scan using shopify_orders_2019_01_03_on_shop_id on shopify_orders_2019_01_03 o_4 (cost=0.42..8,148.85 rows=12,581 width=32) (actual time=0.643..421.546 rows=12,795 loops=1)

  • Index Cond: (shop_id = 80)
24. 1,206.476 1,206.476 ↑ 1.0 29,648 1

Index Scan using shopify_orders_2019_03_06_on_shop_id on shopify_orders_2019_03_06 o_5 (cost=0.42..14,314.38 rows=30,062 width=32) (actual time=0.903..1,206.476 rows=29,648 loops=1)

  • Index Cond: (shop_id = 80)
25. 41.634 41.634 ↑ 1.0 45,000 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_6 (cost=0.42..25,879.05 rows=46,852 width=33) (actual time=0.023..41.634 rows=45,000 loops=1)

  • Index Cond: (shop_id = 80)
26. 15.034 15.034 ↑ 1.0 17,946 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_7 (cost=0.42..8,850.45 rows=18,793 width=32) (actual time=0.023..15.034 rows=17,946 loops=1)

  • Index Cond: (shop_id = 80)
27. 283.670 283.670 ↓ 1.0 7,383 1

Index Scan using shopify_orders_2018_01_06_on_shop_id on shopify_orders_2018_01_06 o_8 (cost=0.42..6,283.22 rows=7,093 width=32) (actual time=0.736..283.670 rows=7,383 loops=1)

  • Index Cond: (shop_id = 80)
28. 1,298.881 1,298.881 ↑ 1.0 24,530 1

Index Scan using shopify_orders_2018_06_12_on_shop_id on shopify_orders_2018_06_12 o_9 (cost=0.42..15,882.76 rows=24,686 width=32) (actual time=1.024..1,298.881 rows=24,530 loops=1)

  • Index Cond: (shop_id = 80)
29. 35.066 35.066 ↑ 1.0 197 1

Index Scan using shopify_orders_2017_01_06_on_shop_id on shopify_orders_2017_01_06 o_10 (cost=0.42..202.65 rows=199 width=32) (actual time=0.640..35.066 rows=197 loops=1)

  • Index Cond: (shop_id = 80)
30. 58.528 58.528 ↑ 1.0 1,817 1

Index Scan using shopify_orders_2017_06_12_on_shop_id on shopify_orders_2017_06_12 o_11 (cost=0.42..1,778.03 rows=1,854 width=31) (actual time=0.998..58.528 rows=1,817 loops=1)

  • Index Cond: (shop_id = 80)
31. 1.439 1.439 ↑ 1.2 16 1

Index Scan using shopify_orders_2016_01_12_on_shop_id on shopify_orders_2016_01_12 o_12 (cost=0.42..16.43 rows=20 width=32) (actual time=0.740..1.439 rows=16 loops=1)

  • Index Cond: (shop_id = 80)
32. 0.355 0.355 ↓ 0.0 0 1

Index Scan using shopify_orders_2015_01_12_on_shop_id on shopify_orders_2015_01_12 o_13 (cost=0.42..2.44 rows=1 width=31) (actual time=0.355..0.355 rows=0 loops=1)

  • Index Cond: (shop_id = 80)
33. 0.007 0.007 ↓ 0.0 0 1

Index Scan using shopify_orders_2014_01_12_on_shop_id on shopify_orders_2014_01_12 o_14 (cost=0.42..1.55 rows=1 width=31) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (shop_id = 80)
34. 0.006 0.006 ↓ 0.0 0 1

Index Scan using shopify_orders_2013_on_shop_id on shopify_orders_2013 o_15 (cost=0.41..1.43 rows=1 width=30) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (shop_id = 80)
35. 47.064 2,872.327 ↑ 1.0 34,862 1

Hash (cost=80,720.12..80,720.12 rows=35,608 width=186) (actual time=2,872.327..2,872.327 rows=34,862 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 2,210kB
36. 37.390 2,825.263 ↑ 1.0 34,862 1

Nested Loop (cost=588.83..80,720.12 rows=35,608 width=186) (actual time=79.693..2,825.263 rows=34,862 loops=1)

37. 94.880 173.223 ↑ 1.0 34,862 1

Bitmap Heap Scan on shopify_refund_line_items rl (cost=588.40..3,694.52 rows=35,608 width=44) (actual time=79.060..173.223 rows=34,862 loops=1)

  • Recheck Cond: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
  • Heap Blocks: exact=1,368
38. 78.343 78.343 ↑ 1.0 34,870 1

Bitmap Index Scan on ix_sv_created_at (cost=0.00..579.50 rows=35,608 width=0) (actual time=78.342..78.343 rows=34,870 loops=1)

  • Index Cond: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
39. 2,614.650 2,614.650 ↑ 1.0 1 34,862

Index Scan using shopify_line_items_pkey on shopify_line_items l_1 (cost=0.43..2.16 rows=1 width=150) (actual time=0.075..0.075 rows=1 loops=34,862)

  • Index Cond: (id = rl.shopify_line_item_id)
40. 0.031 414.171 ↑ 6,609.6 14 1

Subquery Scan on *SELECT* 3 (cost=2,184.31..90,051.84 rows=92,535 width=1,191) (actual time=284.578..414.171 rows=14 loops=1)

41. 35.012 414.140 ↑ 6,609.6 14 1

Hash Join (cost=2,184.31..88,663.81 rows=92,535 width=1,183) (actual time=284.570..414.140 rows=14 loops=1)

  • Hash Cond: (o_16.id = a.shopify_order_id)
42. 20.267 164.403 ↑ 1.0 139,332 1

Append (cost=0.00..82,479.93 rows=142,144 width=32) (actual time=0.027..164.403 rows=139,332 loops=1)

43. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: (shop_id = 80)
44. 9.975 9.975 ↓ 1.0 12,795 1

Index Scan using shopify_orders_2019_01_03_on_shop_id on shopify_orders_2019_01_03 o_17 (cost=0.42..8,298.79 rows=12,581 width=32) (actual time=0.022..9.975 rows=12,795 loops=1)

  • Index Cond: (shop_id = 80)
45. 29.979 29.979 ↑ 1.0 29,648 1

Index Scan using shopify_orders_2019_03_06_on_shop_id on shopify_orders_2019_03_06 o_18 (cost=0.42..14,622.75 rows=30,062 width=32) (actual time=0.018..29.979 rows=29,648 loops=1)

  • Index Cond: (shop_id = 80)
46. 54.391 54.391 ↑ 1.0 45,000 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_19 (cost=0.42..25,879.05 rows=46,852 width=33) (actual time=0.021..54.391 rows=45,000 loops=1)

  • Index Cond: (shop_id = 80)
47. 17.503 17.503 ↑ 1.0 17,946 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_20 (cost=0.42..8,800.12 rows=18,793 width=32) (actual time=0.040..17.503 rows=17,946 loops=1)

  • Index Cond: (shop_id = 80)
48. 6.524 6.524 ↓ 1.0 7,383 1

Index Scan using shopify_orders_2018_01_06_on_shop_id on shopify_orders_2018_01_06 o_21 (cost=0.42..6,283.22 rows=7,093 width=32) (actual time=0.025..6.524 rows=7,383 loops=1)

  • Index Cond: (shop_id = 80)
49. 23.927 23.927 ↑ 1.0 24,530 1

Index Scan using shopify_orders_2018_06_12_on_shop_id on shopify_orders_2018_06_12 o_22 (cost=0.42..15,882.76 rows=24,686 width=32) (actual time=0.024..23.927 rows=24,530 loops=1)

  • Index Cond: (shop_id = 80)
50. 0.246 0.246 ↑ 1.0 197 1

Index Scan using shopify_orders_2017_01_06_on_shop_id on shopify_orders_2017_01_06 o_23 (cost=0.42..202.65 rows=199 width=32) (actual time=0.026..0.246 rows=197 loops=1)

  • Index Cond: (shop_id = 80)
51. 1.523 1.523 ↑ 1.0 1,817 1

Index Scan using shopify_orders_2017_06_12_on_shop_id on shopify_orders_2017_06_12 o_24 (cost=0.42..1,778.03 rows=1,854 width=31) (actual time=0.018..1.523 rows=1,817 loops=1)

  • Index Cond: (shop_id = 80)
52. 0.031 0.031 ↑ 1.2 16 1

Index Scan using shopify_orders_2016_01_12_on_shop_id on shopify_orders_2016_01_12 o_25 (cost=0.42..16.43 rows=20 width=32) (actual time=0.019..0.031 rows=16 loops=1)

  • Index Cond: (shop_id = 80)
53. 0.012 0.012 ↓ 0.0 0 1

Index Scan using shopify_orders_2015_01_12_on_shop_id on shopify_orders_2015_01_12 o_26 (cost=0.42..2.44 rows=1 width=31) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (shop_id = 80)
54. 0.011 0.011 ↓ 0.0 0 1

Index Scan using shopify_orders_2014_01_12_on_shop_id on shopify_orders_2014_01_12 o_27 (cost=0.42..1.55 rows=1 width=31) (actual time=0.010..0.011 rows=0 loops=1)

  • Index Cond: (shop_id = 80)
55. 0.010 0.010 ↓ 0.0 0 1

Index Scan using shopify_orders_2013_on_shop_id on shopify_orders_2013 o_28 (cost=0.41..1.43 rows=1 width=30) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (shop_id = 80)
56. 8.795 214.725 ↓ 1.0 28,175 1

Hash (cost=1,833.66..1,833.66 rows=28,052 width=56) (actual time=214.725..214.725 rows=28,175 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,066kB
57. 205.930 205.930 ↓ 1.0 28,175 1

Index Scan using ix_oa_created_at on shopify_order_adjustments a (cost=0.29..1,833.66 rows=28,052 width=56) (actual time=0.652..205.930 rows=28,175 loops=1)

  • Index Cond: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
58. 26.750 1,958.207 ↓ 252.2 50,444 1

Subquery Scan on *SELECT* 4 (cost=153,380.06..153,385.56 rows=200 width=1,200) (actual time=1,887.426..1,958.207 rows=50,444 loops=1)

59. 83.998 1,931.457 ↓ 252.2 50,444 1

HashAggregate (cost=153,380.06..153,382.06 rows=200 width=1,188) (actual time=1,887.418..1,931.457 rows=50,444 loops=1)

  • Group Key: o_29.id
60. 211.164 1,847.459 ↑ 1.1 53,079 1

Hash Right Join (cost=127,129.03..152,515.39 rows=57,645 width=81) (actual time=1,395.557..1,847.459 rows=53,079 loops=1)

  • Hash Cond: (s.shopify_shipping_line_id = sl.id)
61. 359.710 359.710 ↑ 1.0 624,647 1

Seq Scan on shopify_shipping_tax_lines s (cost=0.00..16,003.74 rows=625,474 width=16) (actual time=0.287..359.710 rows=624,647 loops=1)

62. 34.996 1,276.585 ↑ 1.1 50,444 1

Hash (cost=125,619.47..125,619.47 rows=57,645 width=81) (actual time=1,276.585..1,276.585 rows=50,444 loops=1)

  • Buckets: 32,768 Batches: 2 Memory Usage: 3,215kB
63. 57.558 1,241.589 ↑ 1.1 50,444 1

Nested Loop (cost=0.43..125,619.47 rows=57,645 width=81) (actual time=1.307..1,241.589 rows=50,444 loops=1)

64. 7.099 68.411 ↑ 1.1 50,710 1

Append (cost=0.00..35,293.78 rows=57,278 width=41) (actual time=0.026..68.411 rows=50,710 loops=1)

65. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on shopify_orders o_29 (cost=0.00..0.00 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone) AND (shop_id = 80))
66. 44.571 44.571 ↑ 1.2 32,764 1

Index Scan using shopify_orders_2019_06_09_on_shop_id on shopify_orders_2019_06_09 o_30 (cost=0.42..26,113.31 rows=38,559 width=41) (actual time=0.021..44.571 rows=32,764 loops=1)

  • Index Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 12,236
67. 16.737 16.737 ↑ 1.0 17,946 1

Index Scan using shopify_orders_2019_09_12_on_shop_id on shopify_orders_2019_09_12 o_31 (cost=0.42..8,894.08 rows=18,718 width=40) (actual time=0.025..16.737 rows=17,946 loops=1)

  • Index Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-06-29 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-27 22:59:59'::timestamp without time zone))
68. 1,115.620 1,115.620 ↑ 1.0 1 50,710

Index Scan using index_shopify_shipping_lines_on_shopify_order_id on shopify_shipping_lines sl (cost=0.43..1.57 rows=1 width=48) (actual time=0.022..0.022 rows=1 loops=50,710)

  • Index Cond: (shopify_order_id = o_29.id)
Planning time : 10.996 ms
Execution time : 17,258.027 ms