explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lV0 : Optimization for: plan #4HAX

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 66.162 4,117.574 ↑ 46.3 13,170 1

Unique (cost=1,586,983.61..1,612,121.86 rows=610,341 width=20) (actual time=3,998.156..4,117.574 rows=13,170 loops=1)

2.          

CTE provider_accounts_list

3. 5.374 736.540 ↑ 2.9 48,125 1

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

4. 0.014 0.014 ↑ 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.011..0.014 rows=1 loops=1)

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

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

  • Hash Cond: (public_2.company_id = public_3.id)
6. 575.172 575.172 ↓ 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..575.172 rows=2,043,064 loops=1)

7. 0.033 0.113 ↑ 1.0 108 1

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

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

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

  • Recheck Cond: (provider_id = 125)
  • Heap Blocks: exact=32
9. 0.017 0.017 ↑ 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.017..0.017 rows=108 loops=1)

  • Index Cond: (provider_id = 125)
10. 575.005 4,051.412 ↑ 4.6 1,099,041 1

Sort (cost=1,506,242.48..1,518,811.60 rows=5,027,650 width=20) (actual time=3,998.153..4,051.412 rows=1,099,041 loops=1)

  • Sort Key: public.user_id, public.created_at DESC
  • Sort Method: quicksort Memory: 115,402kB
11. 232.515 3,476.407 ↑ 4.6 1,099,041 1

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

12. 33.804 1,896.392 ↑ 2.9 48,125 1

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

  • Hash Cond: (provider_accounts_list.provider_account_id = public_1.id)
13. 748.498 748.498 ↑ 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.160..748.498 rows=48,125 loops=1)

14. 419.795 1,114.090 ↓ 1.0 2,043,064 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 96,192kB
15. 694.295 694.295 ↓ 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.005..694.295 rows=2,043,064 loops=1)

16. 1,347.500 1,347.500 ↑ 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.028 rows=23 loops=48,125)

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