explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 004rt

Settings
# exclusive inclusive rows x rows loops node
1. 121.250 162,428.382 ↑ 1.0 1 1

Aggregate (cost=105,250.82..105,250.83 rows=1 width=8) (actual time=162,428.381..162,428.382 rows=1 loops=1)

2. 32,862.503 162,307.132 ↓ 69,539.7 1,043,096 1

Nested Loop (cost=20,926.99..105,250.78 rows=15 width=0) (actual time=104,365.109..162,307.132 rows=1,043,096 loops=1)

  • Join Filter: (t4.sellingcurrencysymbol = currency_dollar_conversionrate.currencysymbol)
  • Rows Removed by Join Filter: 186714184
3. 3.634 240.089 ↓ 1.1 180 1

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

4. 2.136 224.035 ↓ 1.1 180 1

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

  • Group Key: currency_dollar_conversionrate.currencysymbol
5. 1.959 221.899 ↓ 1.6 496 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
7. 113.384 209.844 ↓ 1.0 165 3 / 3

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

  • Group Key: currency_dollar_conversionrate.currencysymbol
8. 96.460 96.460 ↑ 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.036..96.460 rows=205,060 loops=3)

9. 12.420 12.420 ↑ 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.069..0.069 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,134.628 129,204.540 ↓ 74,506.9 1,043,096 180

Materialize (cost=10,964.49..94,561.20 rows=14 width=4) (actual time=10.642..717.803 rows=1,043,096 loops=180)

11. 398.045 103,069.912 ↓ 74,506.9 1,043,096 1

Nested Loop (cost=10,964.49..94,561.13 rows=14 width=4) (actual time=1,914.354..103,069.912 rows=1,043,096 loops=1)

12. 32,739.252 98,499.483 ↓ 80,238.2 1,043,096 1

Nested Loop (cost=10,964.06..94,504.66 rows=13 width=20) (actual time=1,914.319..98,499.483 rows=1,043,096 loops=1)

  • Join Filter: (t3.sellingcurrencysymbol = currency_dollar_conversionrate_1.currencysymbol)
  • Rows Removed by Join Filter: 186714184
13. 2.198 222.231 ↓ 1.1 180 1

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

  • Group Key: currency_dollar_conversionrate_1.currencysymbol
14. 2.052 220.033 ↓ 1.6 496 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
16. 113.542 208.848 ↓ 1.0 165 3 / 3

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

  • Group Key: currency_dollar_conversionrate_1.currencysymbol
17. 95.306 95.306 ↑ 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.026..95.306 rows=205,060 loops=3)

18. 27,226.879 65,538.000 ↓ 80,238.2 1,043,096 180

Materialize (cost=1,001.99..84,506.28 rows=13 width=8) (actual time=0.025..364.100 rows=1,043,096 loops=180)

19. 0.000 38,311.121 ↓ 80,238.2 1,043,096 1

Gather (cost=1,001.99..84,506.21 rows=13 width=8) (actual time=3.266..38,311.121 rows=1,043,096 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 747.637 38,477.120 ↓ 69,539.8 347,699 3 / 3

Nested Loop (cost=1.99..83,504.91 rows=5 width=8) (actual time=0.684..38,477.120 rows=347,699 loops=3)

  • Join Filter: (r.parentid = prp.resellerid)
21. 292.768 34,245.056 ↓ 3,196.7 348,443 3 / 3

Nested Loop (cost=1.42..82,880.64 rows=109 width=48) (actual time=0.583..34,245.056 rows=348,443 loops=3)

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

Nested Loop (cost=0.85..81,992.34 rows=23 width=24) (actual time=0.423..4,368.929 rows=60,498 loops=3)

23. 168.714 2,997.081 ↓ 3,781.1 60,498 3 / 3

Nested Loop (cost=0.43..81,857.22 rows=16 width=16) (actual time=0.347..2,997.081 rows=60,498 loops=3)

24. 2,041.897 2,041.897 ↓ 3,781.1 60,498 3 / 3

Parallel Seq Scan on reseller r (cost=0.00..81,722.10 rows=16 width=8) (actual time=0.234..2,041.897 rows=60,498 loops=3)

  • Filter: ((split_part(parentkey, '_'::text, 2) = ANY ('{1,330623}'::text[])) AND (array_length(regexp_split_to_array(parentkey, '_'::text), 1) = 3))
  • Rows Removed by Filter: 194513
25. 786.470 786.470 ↑ 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.013..0.013 rows=1 loops=181,493)

  • Index Cond: (resellerid = r.parentid)
26. 1,270.451 1,270.451 ↑ 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.021..0.021 rows=1 loops=181,493)

  • Index Cond: (resellerid = r.resellerid)
27. 29,583.359 29,583.359 ↑ 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.077..0.489 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: 1040946
Planning time : 14.898 ms
Execution time : 162,452.809 ms