explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YUfu

Settings
# exclusive inclusive rows x rows loops node
1. 121.527 158,797.471 ↑ 1.0 1 1

Aggregate (cost=106,047.38..106,047.39 rows=1 width=8) (actual time=158,797.471..158,797.471 rows=1 loops=1)

2. 32,690.605 158,675.944 ↓ 69,539.7 1,043,096 1

Nested Loop (cost=20,926.99..106,047.35 rows=15 width=0) (actual time=100,817.805..158,675.944 rows=1,043,096 loops=1)

  • Join Filter: (t4.sellingcurrencysymbol = currency_dollar_conversionrate.currencysymbol)
  • Rows Removed by Join Filter: 186,714,184
3. 3.368 212.859 ↓ 1.1 180 1

Nested Loop (cost=9,962.50..10,653.91 rows=170 width=8) (actual time=194.647..212.859 rows=180 loops=1)

4. 2.165 197.251 ↓ 1.1 180 1

Finalize GroupAggregate (cost=9,962.07..9,966.02 rows=158 width=12) (actual time=194.569..197.251 rows=180 loops=1)

  • Group Key: currency_dollar_conversionrate.currencysymbol
5. 1.095 195.086 ↓ 1.6 496 1

Sort (cost=9,962.07..9,962.86 rows=316 width=12) (actual time=194.555..195.086 rows=496 loops=1)

  • Sort Key: currency_dollar_conversionrate.currencysymbol
  • Sort Method: quicksort Memory: 48kB
6. 8.306 193.991 ↓ 1.6 496 1

Gather (cost=9,915.77..9,948.95 rows=316 width=12) (actual time=193.302..193.991 rows=496 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 89.588 185.685 ↓ 1.0 165 3 / 3

Partial HashAggregate (cost=8,915.77..8,917.35 rows=158 width=12) (actual time=185.631..185.685 rows=165 loops=3)

  • Group Key: currency_dollar_conversionrate.currencysymbol
8. 96.097 96.097 ↑ 1.2 205,060 3 / 3

Parallel Seq Scan on currency_dollar_conversionrate (cost=0.00..7,652.85 rows=252,585 width=12) (actual time=0.056..96.097 rows=205,060 loops=3)

9. 12.240 12.240 ↑ 1.0 1 180

Index Only Scan using currency_dollar_conversionrate_date_added_unique on currency_dollar_conversionrate conversion_table_1 (cost=0.42..4.34 rows=1 width=12) (actual time=0.068..0.068 rows=1 loops=180)

  • Index Cond: ((date_added = (max(currency_dollar_conversionrate.date_added))) AND (currencysymbol = currency_dollar_conversionrate.currencysymbol))
  • Heap Fetches: 153
10. 26,226.529 125,772.480 ↓ 74,506.9 1,043,096 180

Materialize (cost=10,964.49..95,357.77 rows=14 width=4) (actual time=13.282..698.736 rows=1,043,096 loops=180)

11. 378.344 99,545.951 ↓ 74,506.9 1,043,096 1

Nested Loop (cost=10,964.49..95,357.70 rows=14 width=4) (actual time=2,389.325..99,545.951 rows=1,043,096 loops=1)

12. 32,317.835 94,995.223 ↓ 80,238.2 1,043,096 1

Nested Loop (cost=10,964.06..95,301.23 rows=13 width=20) (actual time=2,389.273..94,995.223 rows=1,043,096 loops=1)

  • Join Filter: (t3.sellingcurrencysymbol = currency_dollar_conversionrate_1.currencysymbol)
  • Rows Removed by Join Filter: 186,714,184
13. 1.991 180.488 ↓ 1.1 180 1

Finalize GroupAggregate (cost=9,962.07..9,966.02 rows=158 width=12) (actual time=177.685..180.488 rows=180 loops=1)

  • Group Key: currency_dollar_conversionrate_1.currencysymbol
14. 1.500 178.497 ↓ 1.6 496 1

Sort (cost=9,962.07..9,962.86 rows=316 width=12) (actual time=177.677..178.497 rows=496 loops=1)

  • Sort Key: currency_dollar_conversionrate_1.currencysymbol
  • Sort Method: quicksort Memory: 48kB
15. 9.315 176.997 ↓ 1.6 496 1

Gather (cost=9,915.77..9,948.95 rows=316 width=12) (actual time=176.314..176.997 rows=496 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 89.439 167.682 ↓ 1.0 165 3 / 3

Partial HashAggregate (cost=8,915.77..8,917.35 rows=158 width=12) (actual time=167.628..167.682 rows=165 loops=3)

  • Group Key: currency_dollar_conversionrate_1.currencysymbol
17. 78.243 78.243 ↑ 1.2 205,060 3 / 3

Parallel Seq Scan on currency_dollar_conversionrate currency_dollar_conversionrate_1 (cost=0.00..7,652.85 rows=252,585 width=12) (actual time=0.032..78.243 rows=205,060 loops=3)

18. 26,845.222 62,496.900 ↓ 80,238.2 1,043,096 180

Materialize (cost=1,001.99..85,302.84 rows=13 width=8) (actual time=0.019..347.205 rows=1,043,096 loops=180)

19. 0.000 35,651.678 ↓ 80,238.2 1,043,096 1

Gather (cost=1,001.99..85,302.78 rows=13 width=8) (actual time=2.184..35,651.678 rows=1,043,096 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 556.723 35,790.790 ↓ 69,539.8 347,699 3 / 3

Nested Loop (cost=1.99..84,301.48 rows=5 width=8) (actual time=0.695..35,790.790 rows=347,699 loops=3)

  • Join Filter: (r.parentid = prp.resellerid)
21. 247.317 31,749.640 ↓ 3,196.7 348,443 3 / 3

Nested Loop (cost=1.42..83,677.21 rows=109 width=48) (actual time=0.584..31,749.640 rows=348,443 loops=3)

  • Join Filter: (r.resellerid = rp.resellerid)
22. 98.529 3,249.913 ↓ 2,630.3 60,498 3 / 3

Nested Loop (cost=0.85..82,788.91 rows=23 width=24) (actual time=0.254..3,249.913 rows=60,498 loops=3)

23. 161.933 1,820.435 ↓ 3,781.1 60,498 3 / 3

Nested Loop (cost=0.43..82,653.79 rows=16 width=16) (actual time=0.168..1,820.435 rows=60,498 loops=3)

24. 932.530 932.530 ↓ 3,781.1 60,498 3 / 3

Parallel Seq Scan on reseller r (cost=0.00..82,518.67 rows=16 width=8) (actual time=0.063..932.530 rows=60,498 loops=3)

  • Filter: ((split_part(parentkey, '_'::text, 2) = ANY ('{1,330623}'::text[])) AND (split_part(parentkey, '_'::text, 3) <> ''::text) AND (split_part(parentkey, '_'::text, 4) = ''::text))
  • Rows Removed by Filter: 194,513
25. 725.972 725.972 ↑ 1.0 1 181,493 / 3

Index Scan using reseller_settings_resellerid_inherit_tlds on reseller_settings t4 (cost=0.43..8.45 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=181,493)

  • Index Cond: (resellerid = r.parentid)
26. 1,330.949 1,330.949 ↑ 1.0 1 181,493 / 3

Index Scan using reseller_settings_resellerid_inherit_tlds on reseller_settings t3 (cost=0.43..8.45 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=181,493)

  • Index Cond: (resellerid = r.resellerid)
27. 28,252.410 28,252.410 ↑ 6.8 6 181,493 / 3

Index Scan using reseller_hosting_plan_price_pkey on reseller_hosting_plan_price rp (cost=0.57..38.11 rows=41 width=36) (actual time=0.075..0.467 rows=6 loops=181,493)

  • Index Cond: (resellerid = t3.resellerid)
  • Filter: (hosting_type = 'resellerhosting'::text)
  • Rows Removed by Filter: 277
28. 3,484.427 3,484.427 ↑ 1.0 1 1,045,328 / 3

Index Scan using reseller_hosting_plan_price_pkey on reseller_hosting_plan_price prp (cost=0.57..5.71 rows=1 width=36) (actual time=0.010..0.010 rows=1 loops=1,045,328)

  • Index Cond: ((resellerid = t4.resellerid) AND (planid = rp.planid) AND (action_type = rp.action_type) AND (tenure = rp.tenure))
  • Filter: (hosting_type = 'resellerhosting'::text)
29. 4,172.384 4,172.384 ↑ 1.0 1 1,043,096

Index Only Scan using currency_dollar_conversionrate_date_added_unique on currency_dollar_conversionrate conversion_table (cost=0.42..4.34 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=1,043,096)

  • Index Cond: ((date_added = (max(currency_dollar_conversionrate_1.date_added))) AND (currencysymbol = currency_dollar_conversionrate_1.currencysymbol))
  • Heap Fetches: 1,040,946
Planning time : 8.939 ms
Execution time : 158,824.414 ms