explain.depesz.com

PostgreSQL's explain analyze made readable

Result: afDn : Optimization for: Optimization for: plan #yO6o; plan #ZG3J

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9,591.979 33,116.601 ↓ 582.0 1,164 3,173

GroupAggregate (cost=130,675.89..130,675.94 rows=2 width=52) (actual time=6.391..10.437 rows=1,164 loops=3,173)

  • Group Key: li_1.id, c_1.id, ciw.id
2. 3,064.286 23,524.622 ↓ 1,433.0 2,866 3,173

Sort (cost=130,675.89..130,675.89 rows=2 width=64) (actual time=6.387..7.414 rows=2,866 loops=3,173)

  • Sort Key: li_1.id, c_1.id, ciw.id
  • Sort Method: quicksort Memory: 463kB
3. 716.850 20,460.336 ↓ 1,433.0 2,866 1

Gather (cost=129,915.85..130,675.88 rows=2 width=64) (actual time=15,339.349..20,460.336 rows=2,866 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 7.129 19,743.486 ↓ 955.0 955 3 / 3

Nested Loop (cost=128,915.85..129,675.68 rows=1 width=64) (actual time=15,306.025..19,743.486 rows=955 loops=3)

  • Join Filter: (ciw.investment_id = li_1.id)
5. 1,914.182 19,736.357 ↑ 1.0 955 3 / 3

Merge Join (cost=128,915.43..129,238.30 rows=957 width=36) (actual time=15,305.990..19,736.357 rows=955 loops=3)

  • Merge Cond: ((pi.investment_list_id = ciw.investment_id) AND (c_1.id = ciw.client_id))
6. 8,567.656 17,811.381 ↓ 57.7 2,226,864 3 / 3

Sort (cost=128,652.16..128,748.67 rows=38,605 width=28) (actual time=15,289.053..17,811.381 rows=2,226,864 loops=3)

  • Sort Key: pi.investment_list_id, c_1.id
  • Sort Method: external merge Disk: 56960kB
  • Worker 0: Sort Method: external merge Disk: 58016kB
  • Worker 1: Sort Method: external merge Disk: 52704kB
7. 6,426.811 9,243.725 ↓ 57.7 2,226,864 3 / 3

Parallel Hash Join (cost=6,267.02..125,711.13 rows=38,605 width=28) (actual time=5,508.924..9,243.725 rows=2,226,864 loops=3)

  • Hash Cond: (pi.plan_id = big_plans.id)
8. 1,921.711 1,921.711 ↑ 1.3 2,272,526 3 / 3

Parallel Seq Scan on plan_investments pi (cost=0.00..108,564.58 rows=2,840,658 width=28) (actual time=0.030..1,921.711 rows=2,272,526 loops=3)

9. 248.500 895.203 ↓ 37.6 100,357 3 / 3

Parallel Hash (cost=6,233.63..6,233.63 rows=2,671 width=8) (actual time=895.202..895.203 rows=100,357 loops=3)

  • Buckets: 65536 (originally 8192) Batches: 8 (originally 1) Memory Usage: 2048kB
10. 128.695 646.703 ↓ 37.6 100,357 3 / 3

Nested Loop (cost=1,921.40..6,233.63 rows=2,671 width=8) (actual time=98.333..646.703 rows=100,357 loops=3)

11. 17.833 171.109 ↓ 152.9 5,506 3 / 3

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

  • Hash Cond: (companies.id = c_1.company_id)
12. 11.679 150.047 ↓ 7.9 5,506 3 / 3

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

13. 9.615 110.836 ↓ 7.9 5,506 3 / 3

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

  • Hash Cond: (users.person_id = people.id)
14. 6.543 6.543 ↓ 7.9 5,542 3 / 3

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

15. 48.175 94.678 ↑ 1.0 49,650 3 / 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2452kB
16. 46.503 46.503 ↑ 1.0 49,650 3 / 3

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

17. 27.532 27.532 ↑ 1.0 1 16,519 / 3

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

  • Index Cond: (id = people.company_id)
  • Heap Fetches: 2494
18. 1.663 3.229 ↓ 4.7 3,023 3 / 3

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 151kB
19. 1.566 1.566 ↓ 4.7 3,023 3 / 3

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

20. 346.899 346.899 ↑ 4.1 18 16,519 / 3

Index Scan using big_plans__user_id_owner_idx on big_plans (cost=0.42..65.94 rows=73 width=8) (actual time=0.009..0.063 rows=18 loops=16,519)

  • Index Cond: (user_id_owner = users.id)
  • Filter: is_active_client_plan
  • Rows Removed by Filter: 2
21. 10.794 10.794 ↓ 1.1 3,500 3 / 3

Sort (cost=263.27..271.20 rows=3,173 width=12) (actual time=6.812..10.794 rows=3,500 loops=3)

  • Sort Key: ciw.investment_id, ciw.client_id