explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0HAM : no gp

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 24.164 307.500 ↓ 1.1 15,142 1

Hash Right Join (cost=22,711.54..23,197.21 rows=14,058 width=1,752) (actual time=281.632..307.500 rows=15,142 loops=1)

  • Hash Cond: (temp_first_ga_transactions.id = orders.id)
2.          

CTE orders_with_numbers

3. 9.046 23.943 ↓ 1.0 15,142 1

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

4. 6.826 14.897 ↓ 1.0 15,142 1

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

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

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

6.          

CTE order_theme_and_tests

7. 83.871 121.955 ↓ 1.1 15,142 1

GroupAggregate (cost=2,626.97..2,920.96 rows=14,058 width=68) (actual time=34.308..121.955 rows=15,142 loops=1)

  • Group Key: order_items.order_id
8. 19.434 38.084 ↓ 1.1 50,998 1

Sort (cost=2,626.97..2,649.89 rows=45,840 width=43) (actual time=34.272..38.084 rows=50,998 loops=1)

  • Sort Key: order_items.order_id
  • Sort Method: quicksort Memory: 6866kB
9. 13.068 18.650 ↓ 1.1 50,998 1

Hash Join (cost=179.28..1,917.17 rows=45,840 width=43) (actual time=0.718..18.650 rows=50,998 loops=1)

  • Hash Cond: (order_items.panel_id = panels.id)
10. 4.878 4.878 ↑ 1.0 45,190 1

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

11. 0.079 0.704 ↓ 1.1 397 1

Hash (cost=178.07..178.07 rows=346 width=47) (actual time=0.704..0.704 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
12. 0.096 0.625 ↓ 1.1 397 1

Hash Join (cost=76.70..178.07 rows=346 width=47) (actual time=0.379..0.625 rows=397 loops=1)

  • Hash Cond: (panels.id = panel_to_tests.panel_id)
13. 0.158 0.158 ↑ 1.0 355 1

Seq Scan on panels (cost=0.00..100.06 rows=355 width=38) (actual time=0.003..0.158 rows=355 loops=1)

14. 0.058 0.371 ↓ 1.1 397 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
15. 0.097 0.313 ↓ 1.1 397 1

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

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

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

17. 0.054 0.184 ↑ 1.0 348 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
18. 0.130 0.130 ↑ 1.0 348 1

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

19.          

CTE stripe_cross_border_fees

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on orders orders_2 (cost=0.00..3,208.68 rows=15,051 width=36) (never executed)

21.          

CTE extended_orders

22. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,842.18..4,211.71 rows=15,051 width=1,546) (never executed)

  • Hash Cond: (orders_3.patient_service_center_id = patient_service_centers.id)
23. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,253.83..3,352.04 rows=15,051 width=1,399) (never executed)

  • Hash Cond: (stripe_cross_border_fees.id = orders_3.id)
24. 0.000 0.000 ↓ 0.0 0

CTE Scan on stripe_cross_border_fees (cost=0.00..90.31 rows=15,051 width=36) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,201.15..3,201.15 rows=15,051 width=1,367) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on orders orders_3 (cost=0.00..3,201.15 rows=15,051 width=1,367) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=580.09..580.09 rows=2,362 width=7) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on patient_service_centers (cost=0.00..580.09 rows=2,362 width=7) (never executed)

29.          

CTE order_test_contract_cogs

30. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=2,540.58..2,541.14 rows=62 width=68) (never executed)

  • Group Key: extended_orders.id
31. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,540.58..2,540.61 rows=62 width=62) (never executed)

  • Sort Key: extended_orders.id
32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,814.98..2,540.21 rows=62 width=62) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,814.93..2,536.42 rows=64 width=70) (never executed)

  • Hash Cond: (order_items_1.panel_id = panel_to_tests_1.panel_id)
  • Join Filter: (extended_orders.accrual_date <@ quest_test_cogs.effective_dates)
34. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,729.71..2,189.32 rows=50,354 width=52) (never executed)

  • Hash Cond: (extended_orders.id = order_items_1.order_id)
35. 0.000 0.000 ↓ 0.0 0

CTE Scan on extended_orders (cost=0.00..90.31 rows=15,051 width=48) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,565.10..1,565.10 rows=47,032 width=8) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Seq Scan on order_items order_items_1 (cost=0.00..1,565.10 rows=47,032 width=8) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Hash (cost=83.68..83.68 rows=440 width=32) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=76.70..83.68 rows=440 width=32) (never executed)

  • Hash Cond: (quest_test_cogs.ntc_code = tests_1.ntc_code)
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on quest_test_cogs (cost=0.00..5.33 rows=443 width=33) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Hash (cost=75.48..75.48 rows=346 width=9) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=72.26..75.48 rows=346 width=9) (never executed)

  • Hash Cond: (panel_to_tests_1.test_id = tests_1.id)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on panel_to_tests panel_to_tests_1 (cost=0.00..3.04 rows=346 width=8) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Hash (cost=71.04..71.04 rows=348 width=9) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests tests_1 (cost=0.00..71.04 rows=348 width=9) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using panels_pkey on panels panels_1 (cost=0.05..0.06 rows=1 width=4) (never executed)

  • Index Cond: (id = order_items_1.panel_id)
  • Heap Fetches: 0
47.          

CTE order_test_shadow_cogs

48. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2,576.22..2,576.92 rows=200 width=36) (never executed)

  • Group Key: extended_orders_1.id
49. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,832.05..2,477.50 rows=49,359 width=62) (never executed)

  • Hash Cond: (order_items_2.panel_id = panels_2.id)
50. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,729.71..2,189.32 rows=50,354 width=48) (never executed)

  • Hash Cond: (extended_orders_1.id = order_items_2.order_id)
51. 0.000 0.000 ↓ 0.0 0

CTE Scan on extended_orders extended_orders_1 (cost=0.00..90.31 rows=15,051 width=44) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,565.10..1,565.10 rows=47,032 width=8) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on order_items order_items_2 (cost=0.00..1,565.10 rows=47,032 width=8) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Hash (cost=101.12..101.12 rows=348 width=26) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=93.60..101.12 rows=348 width=26) (never executed)

  • Hash Cond: (quest_test_cogs_1.ntc_code = tests_2.ntc_code)
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on quest_test_cogs quest_test_cogs_1 (cost=0.00..6.21 rows=350 width=23) (never executed)

  • Filter: ((timezone('America/New_York'::text, now()))::date <@ effective_dates)
57. 0.000 0.000 ↓ 0.0 0

Hash (cost=92.39..92.39 rows=346 width=13) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=88.98..92.39 rows=346 width=13) (never executed)

  • Hash Cond: (panel_to_tests_2.test_id = tests_2.id)
59. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16.72..19.94 rows=346 width=12) (never executed)

  • Hash Cond: (panel_to_tests_2.panel_id = panels_2.id)
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on panel_to_tests panel_to_tests_2 (cost=0.00..3.04 rows=346 width=8) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Hash (cost=15.47..15.47 rows=355 width=4) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Only Scan using panels_pkey on panels panels_2 (cost=0.05..15.47 rows=355 width=4) (never executed)

  • Heap Fetches: 0
63. 0.000 0.000 ↓ 0.0 0

Hash (cost=71.04..71.04 rows=348 width=9) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests tests_2 (cost=0.00..71.04 rows=348 width=9) (never executed)

65.          

CTE orders_with_cogs

66. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.34..138.93 rows=4,666 width=1,721) (never executed)

  • Hash Cond: (extended_orders_2.id = order_test_contract_cogs.id)
67. 0.000 0.000 ↓ 0.0 0

CTE Scan on extended_orders extended_orders_2 (cost=0.00..90.31 rows=15,051 width=1,625) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.12..2.12 rows=62 width=72) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.59..2.12 rows=62 width=72) (never executed)

  • Hash Cond: (order_test_shadow_cogs.id = order_test_contract_cogs.id)
70. 0.000 0.000 ↓ 0.0 0

CTE Scan on order_test_shadow_cogs (cost=0.00..1.20 rows=200 width=36) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.37..0.37 rows=62 width=36) (never executed)

72. 0.000 0.000 ↓ 0.0 0

CTE Scan on order_test_contract_cogs (cost=0.00..0.37 rows=62 width=36) (never executed)

73.          

CTE orders_with_gross_profit

74. 0.000 0.000 ↓ 0.0 0

CTE Scan on orders_with_cogs (cost=0.00..32.66 rows=4,666 width=1,785) (never executed)

75. 1.752 1.752 ↑ 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.015..1.752 rows=14,696 loops=1)

76. 24.157 281.584 ↓ 1.1 15,142 1

Hash (cost=3,538.53..3,538.53 rows=14,058 width=1,431) (actual time=281.584..281.584 rows=15,142 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 17582kB
77. 16.119 257.427 ↓ 1.1 15,142 1

Hash Join (cost=3,394.77..3,538.53 rows=14,058 width=1,431) (actual time=225.545..257.427 rows=15,142 loops=1)

  • Hash Cond: (orders_with_numbers.id = orders.id)
78. 29.086 29.086 ↓ 1.0 15,142 1

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

79. 26.491 212.222 ↓ 1.1 15,142 1

Hash (cost=3,345.56..3,345.56 rows=14,058 width=1,403) (actual time=212.222..212.222 rows=15,142 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 17613kB
80. 20.096 185.731 ↓ 1.1 15,142 1

Hash Join (cost=3,253.83..3,345.56 rows=14,058 width=1,403) (actual time=71.755..185.731 rows=15,142 loops=1)

  • Hash Cond: (order_theme_and_tests.id = orders.id)
81. 128.222 128.222 ↓ 1.1 15,142 1

CTE Scan on order_theme_and_tests (cost=0.00..84.35 rows=14,058 width=36) (actual time=34.309..128.222 rows=15,142 loops=1)

82. 22.734 37.413 ↓ 1.0 15,142 1

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

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

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

Planning time : 4.091 ms
Execution time : 310.320 ms