explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GZp8p

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 61.789 ↑ 1.0 1 1

Aggregate (cost=125,538.49..125,538.50 rows=1 width=32) (actual time=61.789..61.789 rows=1 loops=1)

2.          

CTE unique_canonical_ids

3. 0.015 0.015 ↑ 100.0 1 1

Function Scan on jsonb_array_elements_text chemical_id (cost=0.00..1.50 rows=100 width=4) (actual time=0.014..0.015 rows=1 loops=1)

4.          

CTE all_needed_chemical_ids

5. 0.016 1.948 ↑ 20.3 13 1

HashAggregate (cost=312.12..314.76 rows=264 width=8) (actual time=1.946..1.948 rows=13 loops=1)

  • Group Key: uci.canonical_chemical_id, cc.id
6. 0.002 1.932 ↑ 20.3 13 1

Append (cost=14.39..310.80 rows=264 width=8) (actual time=0.131..1.932 rows=13 loops=1)

7. 0.844 1.929 ↑ 13.7 12 1

Hash Join (cost=14.39..306.16 rows=164 width=8) (actual time=0.131..1.929 rows=12 loops=1)

  • Hash Cond: (cc.id = cta.original_id)
8. 0.982 0.982 ↑ 1.0 10,628 1

Seq Scan on chemical_chemicals cc (cost=0.00..250.28 rows=10,628 width=4) (actual time=0.006..0.982 rows=10,628 loops=1)

9. 0.007 0.103 ↑ 13.7 12 1

Hash (cost=12.34..12.34 rows=164 width=8) (actual time=0.103..0.103 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.047 0.096 ↑ 13.7 12 1

Hash Join (cost=3.25..12.34 rows=164 width=8) (actual time=0.057..0.096 rows=12 loops=1)

  • Hash Cond: (cta.canonical_id = uci.canonical_chemical_id)
11. 0.030 0.030 ↑ 1.0 396 1

Seq Scan on chemical_true_aliases cta (cost=0.00..5.96 rows=396 width=8) (actual time=0.006..0.030 rows=396 loops=1)

12. 0.002 0.019 ↑ 100.0 1 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.019..0.019 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.017 0.017 ↑ 100.0 1 1

CTE Scan on unique_canonical_ids uci (cost=0.00..2.00 rows=100 width=4) (actual time=0.016..0.017 rows=1 loops=1)

14. 0.001 0.001 ↑ 100.0 1 1

CTE Scan on unique_canonical_ids uci_1 (cost=0.00..2.00 rows=100 width=8) (actual time=0.001..0.001 rows=1 loops=1)

15.          

CTE crop_years_to_filter_by

16. 0.019 0.019 ↑ 50.0 2 1

Function Scan on jsonb_array_elements_text crop_year (cost=0.00..1.50 rows=100 width=4) (actual time=0.019..0.019 rows=2 loops=1)

17.          

CTE filter_by_years

18. 0.006 0.026 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=1) (actual time=0.026..0.026 rows=1 loops=1)

19. 0.020 0.020 ↑ 50.0 2 1

CTE Scan on crop_years_to_filter_by (cost=0.00..2.00 rows=100 width=0) (actual time=0.019..0.020 rows=2 loops=1)

20.          

CTE chemical_filtered_prices

21. 1.703 40.527 ↑ 3.3 2,352 1

Hash Join (cost=4,352.51..5,367.09 rows=7,672 width=180) (actual time=39.420..40.527 rows=2,352 loops=1)

  • Hash Cond: (anci.chemical_id = cp.chemical_id)
22. 0.001 0.001 ↑ 20.3 13 1

CTE Scan on all_needed_chemical_ids anci (cost=0.00..5.28 rows=264 width=4) (actual time=0.000..0.001 rows=13 loops=1)

23. 25.575 38.823 ↑ 1.2 80,935 1

Hash (cost=3,180.88..3,180.88 rows=93,730 width=180) (actual time=38.823..38.823 rows=80,935 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 16,079kB
24. 13.248 13.248 ↓ 1.0 93,734 1

Seq Scan on chemical_prices cp (cost=0.00..3,180.88 rows=93,730 width=180) (actual time=0.007..13.248 rows=93,734 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 3,854
25.          

CTE year_filtered_prices

26. 1.904 44.028 ↑ 609.0 633 1

Nested Loop (cost=0.00..15,500.71 rows=385,518 width=325) (actual time=39.468..44.028 rows=633 loops=1)

  • Join Filter: ((NOT filter_by_years.should_filter) OR (date_part('year'::text, cp_1.crop_year) = (cf.crop_year)::double precision))
  • Rows Removed by Join Filter: 4,071
27. 42.124 42.124 ↑ 3.3 2,352 1

CTE Scan on chemical_filtered_prices cp_1 (cost=0.00..153.44 rows=7,672 width=325) (actual time=39.425..42.124 rows=2,352 loops=1)

28. 0.000 0.000 ↑ 50.0 2 2,352

Materialize (cost=0.00..3.52 rows=100 width=5) (actual time=0.000..0.000 rows=2 loops=2,352)

29. 0.000 0.029 ↑ 50.0 2 1

Nested Loop (cost=0.00..3.02 rows=100 width=5) (actual time=0.028..0.029 rows=2 loops=1)

30. 0.028 0.028 ↑ 1.0 1 1

CTE Scan on filter_by_years (cost=0.00..0.02 rows=1 width=1) (actual time=0.027..0.028 rows=1 loops=1)

31. 0.002 0.002 ↑ 50.0 2 1

CTE Scan on crop_years_to_filter_by cf (cost=0.00..2.00 rows=100 width=4) (actual time=0.001..0.002 rows=2 loops=1)

32.          

CTE year_filtered_quotes

33. 0.122 0.154 ↑ 1,608.9 7 1

Nested Loop (cost=0.00..344.00 rows=11,262 width=103) (actual time=0.148..0.154 rows=7 loops=1)

  • Join Filter: ((NOT filter_by_years_1.should_filter) OR (cpq.crop_year = cf_1.crop_year))
  • Rows Removed by Join Filter: 439
34. 0.032 0.032 ↑ 1.0 223 1

Seq Scan on chemical_price_quotes cpq (cost=0.00..6.23 rows=223 width=103) (actual time=0.009..0.032 rows=223 loops=1)

  • Filter: (deleted_at IS NULL)
35. 0.000 0.000 ↑ 50.0 2 223

Materialize (cost=0.00..3.52 rows=100 width=5) (actual time=0.000..0.000 rows=2 loops=223)

36. 0.003 0.003 ↑ 50.0 2 1

Nested Loop (cost=0.00..3.02 rows=100 width=5) (actual time=0.003..0.003 rows=2 loops=1)

37. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on filter_by_years filter_by_years_1 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

38. 0.000 0.000 ↑ 50.0 2 1

CTE Scan on crop_years_to_filter_by cf_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.000 rows=2 loops=1)

39.          

CTE all_needed_prices

40. 0.055 54.608 ↑ 813.9 633 1

Append (cost=893.35..39,327.44 rows=515,222 width=56) (actual time=48.895..54.608 rows=633 loops=1)

41. 0.207 54.371 ↑ 790.5 633 1

Hash Join (cost=893.35..33,361.41 rows=500,356 width=56) (actual time=48.894..54.371 rows=633 loops=1)

  • Hash Cond: (cp_2.chemical_id = anci_1.chemical_id)
42. 0.454 52.204 ↑ 598.8 633 1

Hash Join (cost=884.77..13,831.40 rows=379,057 width=52) (actual time=46.915..52.204 rows=633 loops=1)

  • Hash Cond: (cp_2.enterprise_id = ent.legacy_id)
43. 44.467 44.467 ↑ 609.0 633 1

CTE Scan on year_filtered_prices cp_2 (cost=0.00..7,710.36 rows=385,518 width=52) (actual time=39.469..44.467 rows=633 loops=1)

44. 3.458 7.283 ↑ 1.0 28,397 1

Hash (cost=529.81..529.81 rows=28,397 width=8) (actual time=7.283..7.283 rows=28,397 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,366kB
45. 3.825 3.825 ↑ 1.0 28,397 1

Seq Scan on enterprises ent (cost=0.00..529.81 rows=28,397 width=8) (actual time=0.009..3.825 rows=28,397 loops=1)

  • Filter: use_analytics
  • Rows Removed by Filter: 484
46. 0.007 1.960 ↑ 20.3 13 1

Hash (cost=5.28..5.28 rows=264 width=8) (actual time=1.960..1.960 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 1.953 1.953 ↑ 20.3 13 1

CTE Scan on all_needed_chemical_ids anci_1 (cost=0.00..5.28 rows=264 width=8) (actual time=1.947..1.953 rows=13 loops=1)

48. 0.000 0.182 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=8.58..962.47 rows=14,866 width=56) (actual time=0.182..0.182 rows=0 loops=1)

49. 0.015 0.182 ↓ 0.0 0 1

Hash Join (cost=8.58..776.65 rows=14,866 width=52) (actual time=0.182..0.182 rows=0 loops=1)

  • Hash Cond: (cpq_1.chemical_id = anci_2.chemical_id)
50. 0.161 0.161 ↑ 1,608.9 7 1

CTE Scan on year_filtered_quotes cpq_1 (cost=0.00..225.24 rows=11,262 width=44) (actual time=0.149..0.161 rows=7 loops=1)

51. 0.004 0.006 ↑ 20.3 13 1

Hash (cost=5.28..5.28 rows=264 width=8) (actual time=0.006..0.006 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.002 0.002 ↑ 20.3 13 1

CTE Scan on all_needed_chemical_ids anci_2 (cost=0.00..5.28 rows=264 width=8) (actual time=0.001..0.002 rows=13 loops=1)

53.          

CTE allowed_price_ranges

54. 1.409 7.311 ↑ 200.0 1 1

HashAggregate (cost=18,032.77..18,135.77 rows=200 width=76) (actual time=7.311..7.311 rows=1 loops=1)

  • Group Key: anp.canonical_chemical_id
55. 5.902 5.902 ↑ 813.9 633 1

CTE Scan on all_needed_prices anp (cost=0.00..10,304.44 rows=515,222 width=8) (actual time=0.001..5.902 rows=633 loops=1)

56.          

CTE prices_in_price_range

57. 0.576 56.899 ↑ 90.4 633 1

Hash Join (cost=6.50..34,783.99 rows=57,247 width=56) (actual time=56.239..56.899 rows=633 loops=1)

  • Hash Cond: (anp_1.canonical_chemical_id = apr.canonical_chemical_id)
  • Join Filter: ((anp_1.normalized_unit_price >= (apr.min_allowed_normalized_price)::double precision) AND (anp_1.normalized_unit_price <= (apr.max_allowed_normalized_price)::double precision))
58. 49.006 49.006 ↑ 813.9 633 1

CTE Scan on all_needed_prices anp_1 (cost=0.00..10,304.44 rows=515,222 width=56) (actual time=48.897..49.006 rows=633 loops=1)

59. 0.003 7.317 ↑ 200.0 1 1

Hash (cost=4.00..4.00 rows=200 width=68) (actual time=7.317..7.317 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 7.314 7.314 ↑ 200.0 1 1

CTE Scan on allowed_price_ranges apr (cost=0.00..4.00 rows=200 width=68) (actual time=7.313..7.314 rows=1 loops=1)

61.          

CTE unit_counts

62. 0.157 57.234 ↑ 2,862.5 2 1

HashAggregate (cost=1,574.29..1,631.54 rows=5,725 width=44) (actual time=57.228..57.234 rows=2 loops=1)

  • Group Key: pipr.canonical_chemical_id, pipr.unit
63. 57.077 57.077 ↑ 90.4 633 1

CTE Scan on prices_in_price_range pipr (cost=0.00..1,144.94 rows=57,247 width=40) (actual time=56.241..57.077 rows=633 loops=1)

64.          

CTE popular_units

65. 0.000 57.249 ↑ 200.0 1 1

Unique (cost=471.83..500.45 rows=200 width=44) (actual time=57.248..57.249 rows=1 loops=1)

66. 0.014 57.249 ↑ 2,862.5 2 1

Sort (cost=471.83..486.14 rows=5,725 width=44) (actual time=57.248..57.249 rows=2 loops=1)

  • Sort Key: uc.canonical_chemical_id, uc.price_count DESC
  • Sort Method: quicksort Memory: 25kB
67. 57.235 57.235 ↑ 2,862.5 2 1

CTE Scan on unit_counts uc (cost=0.00..114.50 rows=5,725 width=44) (actual time=57.229..57.235 rows=2 loops=1)

68.          

CTE enterprise_count

69. 0.157 0.399 ↑ 2,862.5 2 1

GroupAggregate (cost=5,644.20..6,271.06 rows=5,725 width=20) (actual time=0.353..0.399 rows=2 loops=1)

  • Group Key: pipr_1.canonical_chemical_id, pipr_1.crop_year
70. 0.151 0.242 ↑ 90.0 633 1

Sort (cost=5,644.20..5,786.60 rows=56,961 width=16) (actual time=0.199..0.242 rows=633 loops=1)

  • Sort Key: pipr_1.canonical_chemical_id, pipr_1.crop_year
  • Sort Method: quicksort Memory: 74kB
71. 0.091 0.091 ↑ 90.0 633 1

CTE Scan on prices_in_price_range pipr_1 (cost=0.00..1,144.94 rows=56,961 width=16) (actual time=0.000..0.091 rows=633 loops=1)

  • Filter: (enterprise_id IS NOT NULL)
72.          

CTE prices_grouped_chemical_year

73. 0.516 0.573 ↑ 2,862.5 2 1

HashAggregate (cost=1,717.41..1,817.60 rows=5,725 width=52) (actual time=0.537..0.573 rows=2 loops=1)

  • Group Key: pipr_2.canonical_chemical_id, pipr_2.crop_year
74. 0.057 0.057 ↑ 90.4 633 1

CTE Scan on prices_in_price_range pipr_2 (cost=0.00..1,144.94 rows=57,247 width=16) (actual time=0.000..0.057 rows=633 loops=1)

75.          

CTE prices_grouped

76. 0.240 1.252 ↑ 200.0 1 1

GroupAggregate (cost=943.66..1,047.36 rows=200 width=36) (actual time=1.252..1.252 rows=1 loops=1)

  • Group Key: pgcy.canonical_chemical_id
77. 0.004 1.012 ↑ 409.5 2 1

Merge Join (cost=943.66..1,037.72 rows=819 width=60) (actual time=1.006..1.012 rows=2 loops=1)

  • Merge Cond: ((pgcy.canonical_chemical_id = ec.canonical_chemical_id) AND (pgcy.crop_year = ec.crop_year))
78. 0.014 0.603 ↑ 2,862.5 2 1

Sort (cost=471.83..486.14 rows=5,725 width=52) (actual time=0.598..0.603 rows=2 loops=1)

  • Sort Key: pgcy.canonical_chemical_id, pgcy.crop_year
  • Sort Method: quicksort Memory: 37kB
79. 0.589 0.589 ↑ 2,862.5 2 1

CTE Scan on prices_grouped_chemical_year pgcy (cost=0.00..114.50 rows=5,725 width=52) (actual time=0.548..0.589 rows=2 loops=1)

80. 0.003 0.405 ↑ 2,862.5 2 1

Sort (cost=471.83..486.14 rows=5,725 width=20) (actual time=0.405..0.405 rows=2 loops=1)

  • Sort Key: ec.canonical_chemical_id, ec.crop_year
  • Sort Method: quicksort Memory: 25kB
81. 0.402 0.402 ↑ 2,862.5 2 1

CTE Scan on enterprise_count ec (cost=0.00..114.50 rows=5,725 width=20) (actual time=0.355..0.402 rows=2 loops=1)

82.          

CTE prices_grouped_json

83. 0.131 61.746 ↑ 200.0 1 1

HashAggregate (cost=482.94..486.94 rows=200 width=170) (actual time=61.745..61.746 rows=1 loops=1)

  • Group Key: pg.canonical_chemical_id, pg.prices_years, pu.unit, ccomp.physical_state, apr_1.min_allowed_normalized_price, apr_1.max_allowed_normalized_price
84. 0.005 61.615 ↑ 200.0 1 1

Nested Loop (cost=399.16..479.94 rows=200 width=138) (actual time=61.613..61.615 rows=1 loops=1)

85. 0.015 61.601 ↑ 200.0 1 1

Hash Join (cost=398.88..411.17 rows=200 width=136) (actual time=61.600..61.601 rows=1 loops=1)

  • Hash Cond: (pg.canonical_chemical_id = pu.canonical_chemical_id)
86. 0.017 4.332 ↑ 200.0 1 1

Hash Join (cost=392.38..397.17 rows=200 width=112) (actual time=4.331..4.332 rows=1 loops=1)

  • Hash Cond: (pg.canonical_chemical_id = cc_1.id)
87. 1.260 1.260 ↑ 200.0 1 1

CTE Scan on prices_grouped pg (cost=0.00..4.00 rows=200 width=36) (actual time=1.260..1.260 rows=1 loops=1)

88. 0.005 3.055 ↑ 200.0 1 1

Hash (cost=389.88..389.88 rows=200 width=76) (actual time=3.055..3.055 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
89. 0.090 3.050 ↑ 200.0 1 1

Hash Join (cost=383.13..389.88 rows=200 width=76) (actual time=3.049..3.050 rows=1 loops=1)

  • Hash Cond: (apr_1.canonical_chemical_id = cc_1.id)
90. 0.000 0.000 ↑ 200.0 1 1

CTE Scan on allowed_price_ranges apr_1 (cost=0.00..4.00 rows=200 width=68) (actual time=0.000..0.000 rows=1 loops=1)

91. 1.351 2.960 ↑ 1.0 10,628 1

Hash (cost=250.28..250.28 rows=10,628 width=8) (actual time=2.960..2.960 rows=10,628 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 544kB
92. 1.609 1.609 ↑ 1.0 10,628 1

Seq Scan on chemical_chemicals cc_1 (cost=0.00..250.28 rows=10,628 width=8) (actual time=0.010..1.609 rows=10,628 loops=1)

93. 0.004 57.254 ↑ 200.0 1 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=57.254..57.254 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
94. 57.250 57.250 ↑ 200.0 1 1

CTE Scan on popular_units pu (cost=0.00..4.00 rows=200 width=36) (actual time=57.249..57.250 rows=1 loops=1)

95. 0.009 0.009 ↑ 1.0 1 1

Index Scan using chemical_compositions_pkey on chemical_compositions ccomp (cost=0.28..0.33 rows=1 width=10) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = cc_1.chemical_composition_id)
96. 61.768 61.768 ↑ 200.0 1 1

CTE Scan on prices_grouped_json pgj (cost=0.00..4.00 rows=200 width=36) (actual time=61.767..61.768 rows=1 loops=1)

Planning time : 1.341 ms
Execution time : 62.294 ms