explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4HAX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 78.346 4,860.777 ↑ 381.8 13,170 1

Unique (cost=2,350,257.07..2,387,964.45 rows=5,027,650 width=20) (actual time=4,732.399..4,860.777 rows=13,170 loops=1)

2.          

CTE provider_accounts_list

3. 5.244 730.831 ↑ 2.9 48,125 1

Nested Loop (cost=347.03..80,741.13 rows=141,429 width=4) (actual time=0.120..730.831 rows=48,125 loops=1)

4. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using providers_pkey on providers public_4 (cost=0.27..8.29 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=1)

  • Index Cond: (id = 125)
  • Heap Fetches: 1
5. 148.315 725.575 ↑ 2.9 48,125 1

Hash Join (cost=346.75..79,318.55 rows=141,429 width=8) (actual time=0.109..725.575 rows=48,125 loops=1)

  • Hash Cond: (public_2.company_id = public_3.id)
6. 577.166 577.166 ↓ 1.0 2,043,064 1

Seq Scan on provider_accounts public_2 (cost=0.00..73,601.76 rows=2,040,176 width=12) (actual time=0.006..577.166 rows=2,043,064 loops=1)

7. 0.015 0.094 ↑ 1.0 108 1

Hash (cost=345.40..345.40 rows=108 width=8) (actual time=0.094..0.094 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
8. 0.065 0.079 ↑ 1.0 108 1

Bitmap Heap Scan on companies public_3 (cost=9.11..345.40 rows=108 width=8) (actual time=0.020..0.079 rows=108 loops=1)

  • Recheck Cond: (provider_id = 125)
  • Heap Blocks: exact=32
9. 0.014 0.014 ↑ 1.0 108 1

Bitmap Index Scan on index_companies_on_provider_id (cost=0.00..9.09 rows=108 width=0) (actual time=0.014..0.014 rows=108 loops=1)

  • Index Cond: (provider_id = 125)
10. 268.090 4,782.431 ↑ 4.6 1,099,040 1

Sort (cost=2,269,515.94..2,282,085.07 rows=5,027,650 width=20) (actual time=4,732.397..4,782.431 rows=1,099,040 loops=1)

  • Sort Key: public.user_id, (first_value(public.new_balance_cents) OVER (?))
  • Sort Method: quicksort Memory: 115,401kB
11. 384.914 4,514.341 ↑ 4.6 1,099,040 1

WindowAgg (cost=1,506,242.48..1,606,795.48 rows=5,027,650 width=20) (actual time=4,072.936..4,514.341 rows=1,099,040 loops=1)

12. 572.269 4,129.427 ↑ 4.6 1,099,040 1

Sort (cost=1,506,242.48..1,518,811.60 rows=5,027,650 width=20) (actual time=4,072.922..4,129.427 rows=1,099,040 loops=1)

  • Sort Key: public.user_id, public.created_at DESC
  • Sort Method: quicksort Memory: 115,401kB
13. 199.314 3,557.158 ↑ 4.6 1,099,040 1

Nested Loop (cost=99,104.53..843,522.01 rows=5,027,650 width=20) (actual time=1,144.312..3,557.158 rows=1,099,040 loops=1)

14. 34.917 1,914.094 ↑ 2.9 48,125 1

Hash Join (cost=99,103.96..102,303.79 rows=141,429 width=4) (actual time=1,143.987..1,914.094 rows=48,125 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = public_1.id)
15. 742.747 742.747 ↑ 2.9 48,125 1

CTE Scan on provider_accounts_list (cost=0.00..2,828.58 rows=141,429 width=4) (actual time=0.122..742.747 rows=48,125 loops=1)

16. 448.559 1,136.430 ↓ 1.0 2,043,064 1

Hash (cost=73,601.76..73,601.76 rows=2,040,176 width=8) (actual time=1,136.430..1,136.430 rows=2,043,064 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 96,192kB
17. 687.871 687.871 ↓ 1.0 2,043,064 1

Seq Scan on provider_accounts public_1 (cost=0.00..73,601.76 rows=2,040,176 width=8) (actual time=0.004..687.871 rows=2,043,064 loops=1)

18. 1,443.750 1,443.750 ↑ 4.7 23 48,125

Index Scan using index_available_balance_changes_on_user_id on available_balance_changes public (cost=0.56..4.17 rows=107 width=20) (actual time=0.005..0.030 rows=23 loops=48,125)

  • Index Cond: (user_id = public_1.user_id)
Planning time : 1.247 ms
Execution time : 4,885.152 ms