explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xMrp

Settings
# exclusive inclusive rows x rows loops node
1. 5.492 52,638.225 ↓ 955.0 955 3

Nested Loop (cost=90,094.07..90,180.55 rows=1 width=12) (actual time=13,462.210..17,546.075 rows=955 loops=3)

  • Join Filter: (ciw.investment_id = li_1.id)
2. 5,397.264 52,612.671 ↓ 12.1 955 3

Merge Join (cost=90,093.65..90,142.04 rows=79 width=16) (actual time=13,462.170..17,537.557 rows=955 loops=3)

  • Merge Cond: ((pi.investment_list_id = ciw.investment_id) AND (c_1.id = ciw.client_id))
3. 20,607.369 47,184.792 ↓ 700.9 2,226,864 3

Sort (cost=89,830.53..89,838.47 rows=3,177 width=8) (actual time=13,445.232..15,728.264 rows=2,226,864 loops=3)

  • Sort Key: pi.investment_list_id, c_1.id
  • Sort Method: external merge Disk: 40,560kB
  • Worker 0: Sort Method: external merge Disk: 40,792kB
  • Worker 1: Sort Method: external merge Disk: 36,440kB
4. 18,379.476 26,577.423 ↓ 700.9 2,226,864 3

Parallel Hash Join (cost=6,254.37..89,645.73 rows=3,177 width=8) (actual time=5,582.753..8,859.141 rows=2,226,864 loops=3)

  • Hash Cond: (pi.plan_id = big_plans.id)
5. 5,923.392 5,923.392 ↓ 9.7 2,272,526 3

Parallel Seq Scan on plan_investments pi (cost=0.00..82,495.94 rows=233,794 width=8) (actual time=0.026..1,974.464 rows=2,272,526 loops=3)

6. 589.569 2,274.555 ↓ 37.8 100,357 3

Parallel Hash (cost=6,221.19..6,221.19 rows=2,655 width=8) (actual time=758.185..758.185 rows=100,357 loops=3)

  • Buckets: 65,536 (originally 8192) Batches: 8 (originally 1) Memory Usage: 2,048kB
7. 364.823 1,684.986 ↓ 37.8 100,357 3

Nested Loop (cost=1,921.40..6,221.19 rows=2,655 width=8) (actual time=90.683..561.662 rows=100,357 loops=3)

8. 16.896 461.175 ↓ 152.9 5,506 3

Hash Join (cost=1,920.98..3,833.48 rows=36 width=8) (actual time=90.625..153.725 rows=5,506 loops=3)

  • Hash Cond: (companies.id = c_1.company_id)
9. 51.332 430.701 ↓ 7.9 5,506 3

Nested Loop (cost=1,799.54..3,710.20 rows=699 width=12) (actual time=86.080..143.567 rows=5,506 loops=3)

10. 37.137 313.293 ↓ 7.9 5,506 3

Hash Join (cost=1,799.12..2,995.95 rows=699 width=8) (actual time=86.035..104.431 rows=5,506 loops=3)

  • Hash Cond: (users.person_id = people.id)
11. 18.981 18.981 ↓ 7.9 5,542 3

Parallel Seq Scan on users (cost=0.00..1,194.99 rows=699 width=8) (actual time=0.009..6.327 rows=5,542 loops=3)

12. 161.343 257.175 ↑ 1.0 49,650 3

Hash (cost=1,178.50..1,178.50 rows=49,650 width=8) (actual time=85.724..85.725 rows=49,650 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,452kB
13. 95.832 95.832 ↑ 1.0 49,650 3

Seq Scan on people (cost=0.00..1,178.50 rows=49,650 width=8) (actual time=0.012..31.944 rows=49,650 loops=3)

14. 66.076 66.076 ↑ 1.0 1 16,519

Index Only Scan using companies_pkey on companies (cost=0.41..1.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=16,519)

  • Index Cond: (id = people.company_id)
  • Heap Fetches: 2,494
15. 8.949 13.578 ↓ 4.7 3,023 3

Hash (cost=113.42..113.42 rows=642 width=8) (actual time=4.526..4.526 rows=3,023 loops=3)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 151kB
16. 4.629 4.629 ↓ 4.7 3,023 3

Seq Scan on clients c_1 (cost=0.00..113.42 rows=642 width=8) (actual time=0.016..1.543 rows=3,023 loops=3)

17. 858.988 858.988 ↑ 4.1 18 16,519

Index Scan using big_plans__user_id_owner_idx on big_plans (cost=0.42..65.60 rows=73 width=8) (actual time=0.007..0.052 rows=18 loops=16,519)

  • Index Cond: (user_id_owner = users.id)
  • Filter: is_active_client_plan
  • Rows Removed by Filter: 2
18. 16.488 30.615 ↓ 1.1 3,505 3

Sort (cost=263.12..271.04 rows=3,171 width=12) (actual time=8.486..10.205 rows=3,505 loops=3)

  • Sort Key: ciw.investment_id, ciw.client_id
  • Sort Method: quicksort Memory: 245kB
  • Worker 0: Sort Method: quicksort Memory: 245kB
  • Worker 1: Sort Method: quicksort Memory: 245kB
19. 14.127 14.127 ↓ 1.0 3,173 3

Seq Scan on client_investment_watches ciw (cost=0.00..78.71 rows=3,171 width=12) (actual time=0.025..4.709 rows=3,173 loops=3)

20. 20.062 20.062 ↑ 1.0 1 2,866

Index Only Scan using investment_lists_pkey on investment_lists li_1 (cost=0.42..0.48 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=2,866)

  • Index Cond: (id = pi.investment_list_id)
  • Heap Fetches: 2,476