explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E3g1 : no theme

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 26.389 768.290 ↑ 23.2 15,142 1

Merge Join (cost=17,035.24..20,726.87 rows=351,140 width=2,202) (actual time=738.756..768.290 rows=15,142 loops=1)

  • Merge Cond: (orders_with_gross_margin.id = orders_with_numbers.id)
2.          

CTE orders_with_numbers

3. 7.829 22.210 ↓ 1.0 15,142 1

WindowAgg (cost=3,410.02..3,492.80 rows=15,051 width=48) (actual time=13.387..22.210 rows=15,142 loops=1)

4. 6.378 14.381 ↓ 1.0 15,142 1

Sort (cost=3,410.02..3,417.55 rows=15,051 width=16) (actual time=13.378..14.381 rows=15,142 loops=1)

  • Sort Key: orders.user_id, orders.inserted_at
  • Sort Method: quicksort Memory: 1094kB
5. 8.003 8.003 ↓ 1.0 15,142 1

Seq Scan on orders (cost=0.00..3,201.15 rows=15,051 width=16) (actual time=0.016..8.003 rows=15,142 loops=1)

6.          

CTE stripe_cross_border_fees

7. 16.024 16.024 ↓ 1.0 15,142 1

Seq Scan on orders orders_1 (cost=0.00..3,208.68 rows=15,051 width=36) (actual time=0.006..16.024 rows=15,142 loops=1)

8.          

CTE extended_orders

9. 46.316 126.513 ↓ 1.0 15,142 1

Hash Join (cost=3,842.18..4,211.71 rows=15,051 width=1,546) (actual time=39.265..126.513 rows=15,142 loops=1)

  • Hash Cond: (orders_2.patient_service_center_id = patient_service_centers.id)
10. 16.961 78.375 ↓ 1.0 15,142 1

Hash Join (cost=3,253.83..3,352.04 rows=15,051 width=1,399) (actual time=37.410..78.375 rows=15,142 loops=1)

  • Hash Cond: (stripe_cross_border_fees.id = orders_2.id)
11. 24.045 24.045 ↓ 1.0 15,142 1

CTE Scan on stripe_cross_border_fees (cost=0.00..90.31 rows=15,051 width=36) (actual time=0.008..24.045 rows=15,142 loops=1)

12. 22.545 37.369 ↓ 1.0 15,142 1

Hash (cost=3,201.15..3,201.15 rows=15,051 width=1,367) (actual time=37.369..37.369 rows=15,142 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 17413kB
13. 14.824 14.824 ↓ 1.0 15,142 1

Seq Scan on orders orders_2 (cost=0.00..3,201.15 rows=15,051 width=1,367) (actual time=0.003..14.824 rows=15,142 loops=1)

14. 0.335 1.822 ↑ 1.0 2,362 1

Hash (cost=580.09..580.09 rows=2,362 width=7) (actual time=1.822..1.822 rows=2,362 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 125kB
15. 1.487 1.487 ↑ 1.0 2,362 1

Seq Scan on patient_service_centers (cost=0.00..580.09 rows=2,362 width=7) (actual time=0.022..1.487 rows=2,362 loops=1)

16.          

CTE order_test_contract_cogs

17. 30.731 182.513 ↓ 244.2 15,142 1

GroupAggregate (cost=2,540.58..2,541.14 rows=62 width=68) (actual time=147.539..182.513 rows=15,142 loops=1)

  • Group Key: extended_orders.id
18. 22.704 151.782 ↓ 822.5 50,998 1

Sort (cost=2,540.58..2,540.61 rows=62 width=62) (actual time=147.525..151.782 rows=50,998 loops=1)

  • Sort Key: extended_orders.id
  • Sort Method: quicksort Memory: 5521kB
19. 8.489 129.078 ↓ 822.5 50,998 1

Nested Loop (cost=1,814.98..2,540.21 rows=62 width=62) (actual time=14.783..129.078 rows=50,998 loops=1)

20. 32.614 69.591 ↓ 796.8 50,998 1

Hash Join (cost=1,814.93..2,536.42 rows=64 width=70) (actual time=14.765..69.591 rows=50,998 loops=1)

  • Hash Cond: (order_items.panel_id = panel_to_tests.panel_id)
  • Join Filter: (extended_orders.accrual_date <@ quest_test_cogs.effective_dates)
  • Rows Removed by Join Filter: 62588
21. 16.661 36.246 ↑ 1.1 45,190 1

Hash Join (cost=1,729.71..2,189.32 rows=50,354 width=52) (actual time=14.023..36.246 rows=45,190 loops=1)

  • Hash Cond: (extended_orders.id = order_items.order_id)
22. 5.905 5.905 ↓ 1.0 15,142 1

CTE Scan on extended_orders (cost=0.00..90.31 rows=15,051 width=48) (actual time=0.001..5.905 rows=15,142 loops=1)

23. 6.709 13.680 ↑ 1.0 45,190 1

Hash (cost=1,565.10..1,565.10 rows=47,032 width=8) (actual time=13.680..13.680 rows=45,190 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2278kB
24. 6.971 6.971 ↑ 1.0 45,190 1

Seq Scan on order_items (cost=0.00..1,565.10 rows=47,032 width=8) (actual time=0.007..6.971 rows=45,190 loops=1)

25. 0.110 0.731 ↓ 1.3 583 1

Hash (cost=83.68..83.68 rows=440 width=32) (actual time=0.731..0.731 rows=583 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
26. 0.178 0.621 ↓ 1.3 583 1

Hash Join (cost=76.70..83.68 rows=440 width=32) (actual time=0.417..0.621 rows=583 loops=1)

  • Hash Cond: (quest_test_cogs.ntc_code = tests.ntc_code)
27. 0.044 0.044 ↑ 1.0 443 1

Seq Scan on quest_test_cogs (cost=0.00..5.33 rows=443 width=33) (actual time=0.009..0.044 rows=443 loops=1)

28. 0.065 0.399 ↓ 1.1 397 1

Hash (cost=75.48..75.48 rows=346 width=9) (actual time=0.399..0.399 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
29. 0.101 0.334 ↓ 1.1 397 1

Hash Join (cost=72.26..75.48 rows=346 width=9) (actual time=0.212..0.334 rows=397 loops=1)

  • Hash Cond: (panel_to_tests.test_id = tests.id)
30. 0.036 0.036 ↓ 1.1 397 1

Seq Scan on panel_to_tests (cost=0.00..3.04 rows=346 width=8) (actual time=0.008..0.036 rows=397 loops=1)

31. 0.055 0.197 ↑ 1.0 348 1

Hash (cost=71.04..71.04 rows=348 width=9) (actual time=0.197..0.197 rows=348 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
32. 0.142 0.142 ↑ 1.0 348 1

Seq Scan on tests (cost=0.00..71.04 rows=348 width=9) (actual time=0.004..0.142 rows=348 loops=1)

33. 50.998 50.998 ↑ 1.0 1 50,998

Index Only Scan using panels_pkey on panels (cost=0.05..0.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=50,998)

  • Index Cond: (id = order_items.panel_id)
  • Heap Fetches: 0
34.          

CTE order_test_shadow_cogs

35. 33.652 207.778 ↓ 75.7 15,142 1

HashAggregate (cost=2,576.22..2,576.92 rows=200 width=36) (actual time=200.158..207.778 rows=15,142 loops=1)

  • Group Key: extended_orders_1.id
36. 14.107 174.126 ↓ 1.0 50,998 1

Hash Join (cost=1,832.05..2,477.50 rows=49,359 width=62) (actual time=15.340..174.126 rows=50,998 loops=1)

  • Hash Cond: (order_items_1.panel_id = panels_1.id)
37. 17.897 158.874 ↑ 1.1 45,190 1

Hash Join (cost=1,729.71..2,189.32 rows=50,354 width=48) (actual time=14.177..158.874 rows=45,190 loops=1)

  • Hash Cond: (extended_orders_1.id = order_items_1.order_id)
38. 127.140 127.140 ↓ 1.0 15,142 1

CTE Scan on extended_orders extended_orders_1 (cost=0.00..90.31 rows=15,051 width=44) (actual time=0.001..127.140 rows=15,142 loops=1)

39. 6.911 13.837 ↑ 1.0 45,190 1

Hash (cost=1,565.10..1,565.10 rows=47,032 width=8) (actual time=13.837..13.837 rows=45,190 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2278kB
40. 6.926 6.926 ↑ 1.0 45,190 1

Seq Scan on order_items order_items_1 (cost=0.00..1,565.10 rows=47,032 width=8) (actual time=0.007..6.926 rows=45,190 loops=1)

41. 0.088 1.145 ↓ 1.1 395 1

Hash (cost=101.12..101.12 rows=348 width=26) (actual time=1.145..1.145 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
42. 0.136 1.057 ↓ 1.1 395 1

Hash Join (cost=93.60..101.12 rows=348 width=26) (actual time=0.667..1.057 rows=395 loops=1)

  • Hash Cond: (quest_test_cogs_1.ntc_code = tests_1.ntc_code)
43. 0.284 0.284 ↑ 1.0 346 1

Seq Scan on quest_test_cogs quest_test_cogs_1 (cost=0.00..6.21 rows=350 width=23) (actual time=0.015..0.284 rows=346 loops=1)

  • Filter: ((timezone('America/New_York'::text, now()))::date <@ effective_dates)
  • Rows Removed by Filter: 97
44. 0.084 0.637 ↓ 1.1 397 1

Hash (cost=92.39..92.39 rows=346 width=13) (actual time=0.637..0.637 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
45. 0.097 0.553 ↓ 1.1 397 1

Hash Join (cost=88.98..92.39 rows=346 width=13) (actual time=0.349..0.553 rows=397 loops=1)

  • Hash Cond: (panel_to_tests_1.test_id = tests_1.id)
46. 0.102 0.259 ↓ 1.1 397 1

Hash Join (cost=16.72..19.94 rows=346 width=12) (actual time=0.139..0.259 rows=397 loops=1)

  • Hash Cond: (panel_to_tests_1.panel_id = panels_1.id)
47. 0.038 0.038 ↓ 1.1 397 1

Seq Scan on panel_to_tests panel_to_tests_1 (cost=0.00..3.04 rows=346 width=8) (actual time=0.007..0.038 rows=397 loops=1)

48. 0.053 0.119 ↑ 1.0 355 1

Hash (cost=15.47..15.47 rows=355 width=4) (actual time=0.119..0.119 rows=355 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
49. 0.066 0.066 ↑ 1.0 355 1

Index Only Scan using panels_pkey on panels panels_1 (cost=0.05..15.47 rows=355 width=4) (actual time=0.021..0.066 rows=355 loops=1)

  • Heap Fetches: 0
50. 0.064 0.197 ↑ 1.0 348 1

Hash (cost=71.04..71.04 rows=348 width=9) (actual time=0.197..0.197 rows=348 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
51. 0.133 0.133 ↑ 1.0 348 1

Seq Scan on tests tests_1 (cost=0.00..71.04 rows=348 width=9) (actual time=0.004..0.133 rows=348 loops=1)

52.          

CTE orders_with_cogs

53. 30.302 489.363 ↓ 3.2 15,142 1

Hash Join (cost=2.34..138.93 rows=4,666 width=1,721) (actual time=449.499..489.363 rows=15,142 loops=1)

  • Hash Cond: (extended_orders_2.id = order_test_contract_cogs.id)
54. 48.859 48.859 ↓ 1.0 15,142 1

CTE Scan on extended_orders extended_orders_2 (cost=0.00..90.31 rows=15,051 width=1,625) (actual time=39.270..48.859 rows=15,142 loops=1)

55. 2.963 410.202 ↓ 244.2 15,142 1

Hash (cost=2.12..2.12 rows=62 width=72) (actual time=410.202..410.202 rows=15,142 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 954kB
56. 4.996 407.239 ↓ 244.2 15,142 1

Hash Join (cost=0.59..2.12 rows=62 width=72) (actual time=390.748..407.239 rows=15,142 loops=1)

  • Hash Cond: (order_test_shadow_cogs.id = order_test_contract_cogs.id)
57. 211.670 211.670 ↓ 75.7 15,142 1

CTE Scan on order_test_shadow_cogs (cost=0.00..1.20 rows=200 width=36) (actual time=200.160..211.670 rows=15,142 loops=1)

58. 2.521 190.573 ↓ 244.2 15,142 1

Hash (cost=0.37..0.37 rows=62 width=36) (actual time=190.573..190.573 rows=15,142 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 778kB
59. 188.052 188.052 ↓ 244.2 15,142 1

CTE Scan on order_test_contract_cogs (cost=0.00..0.37 rows=62 width=36) (actual time=147.541..188.052 rows=15,142 loops=1)

60.          

CTE orders_with_gross_profit

61. 539.751 539.751 ↓ 3.2 15,142 1

CTE Scan on orders_with_cogs (cost=0.00..32.66 rows=4,666 width=1,785) (actual time=449.509..539.751 rows=15,142 loops=1)

62.          

CTE orders_with_gross_margin

63. 597.687 597.687 ↓ 3.2 15,142 1

CTE Scan on orders_with_gross_profit (cost=0.00..37.33 rows=4,666 width=1,849) (actual time=449.520..597.687 rows=15,142 loops=1)

64. 37.314 709.619 ↓ 3.2 15,142 1

Sort (cost=495.89..498.22 rows=4,666 width=1,959) (actual time=707.400..709.619 rows=15,142 loops=1)

  • Sort Key: orders_with_gross_margin.id
  • Sort Method: quicksort Memory: 29086kB
65. 19.831 672.305 ↓ 3.2 15,142 1

Hash Left Join (cost=393.52..439.02 rows=4,666 width=1,959) (actual time=459.165..672.305 rows=15,142 loops=1)

  • Hash Cond: (orders_with_gross_margin.id = temp_first_ga_transactions.id)
66. 642.934 642.934 ↓ 3.2 15,142 1

CTE Scan on orders_with_gross_margin (cost=0.00..28.00 rows=4,666 width=1,849) (actual time=449.527..642.934 rows=15,142 loops=1)

67. 5.707 9.540 ↑ 1.0 14,696 1

Hash (cost=342.09..342.09 rows=14,696 width=114) (actual time=9.540..9.540 rows=14,696 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2296kB
68. 3.833 3.833 ↑ 1.0 14,696 1

Seq Scan on temp_first_ga_transactions (cost=0.00..342.09 rows=14,696 width=114) (actual time=0.014..3.833 rows=14,696 loops=1)

69. 5.217 32.282 ↓ 1.0 15,142 1

Sort (cost=299.18..306.70 rows=15,051 width=36) (actual time=31.342..32.282 rows=15,142 loops=1)

  • Sort Key: orders_with_numbers.id
  • Sort Method: quicksort Memory: 1094kB
70. 27.065 27.065 ↓ 1.0 15,142 1

CTE Scan on orders_with_numbers (cost=0.00..90.31 rows=15,051 width=36) (actual time=13.389..27.065 rows=15,142 loops=1)

Planning time : 3.757 ms
Execution time : 783.226 ms