explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.007 24,892.162 ↓ 8.0 8 1

Unique (cost=130,801.25..130,801.26 rows=1 width=193) (actual time=24,892.155..24,892.162 rows=8 loops=1)

2. 0.016 24,892.155 ↓ 8.0 8 1

Sort (cost=130,801.25..130,801.26 rows=1 width=193) (actual time=24,892.154..24,892.155 rows=8 loops=1)

  • Sort Key: watches.id
  • Sort Method: quicksort Memory: 26kB
3. 0.742 24,892.139 ↓ 8.0 8 1

Subquery Scan on watches (cost=130,801.20..130,801.24 rows=1 width=193) (actual time=24,888.266..24,892.139 rows=8 loops=1)

  • Filter: ((watches.row_number = 1) AND (watches.company_id = 2032883))
  • Rows Removed by Filter: 3375
4. 3.756 24,891.397 ↓ 3,383.0 3,383 1

WindowAgg (cost=130,801.20..130,801.23 rows=1 width=177) (actual time=24,886.824..24,891.397 rows=3,383 loops=1)

5. 5.076 24,887.641 ↓ 3,383.0 3,383 1

Sort (cost=130,801.20..130,801.21 rows=1 width=169) (actual time=24,886.809..24,887.641 rows=3,383 loops=1)

  • Sort Key: li.id, cw.client_id, cw.id DESC
  • Sort Method: quicksort Memory: 579kB
6. 827.693 24,882.565 ↓ 3,383.0 3,383 1

Nested Loop Left Join (cost=130,705.18..130,801.19 rows=1 width=169) (actual time=16,535.667..24,882.565 rows=3,383 loops=1)

  • Join Filter: ((cw.client_id = c_1.id) AND (cw.investment_id = li_1.id))
  • Rows Removed by Join Filter: 3691998
7. 3.239 38.435 ↓ 3,173.0 3,173 1

Nested Loop (cost=29.60..125.50 rows=1 width=129) (actual time=0.156..38.435 rows=3,173 loops=1)

8. 5.449 22.504 ↓ 3,173.0 3,173 1

Nested Loop (cost=29.18..120.67 rows=1 width=88) (actual time=0.139..22.504 rows=3,173 loops=1)

9. 3.026 7.536 ↓ 3,173.0 3,173 1

Hash Join (cost=28.90..120.20 rows=1 width=84) (actual time=0.127..7.536 rows=3,173 loops=1)

  • Hash Cond: (cw.watch_list_reason_picklist_id = plv_reason.value)
10. 3.033 4.449 ↓ 66.1 3,173 1

Hash Join (cost=14.45..105.56 rows=48 width=52) (actual time=0.052..4.449 rows=3,173 loops=1)

  • Hash Cond: (cw.watch_list_status_picklist_id = plv_status.value)
11. 1.381 1.381 ↑ 1.0 3,173 1

Seq Scan on client_investment_watches cw (cost=0.00..78.73 rows=3,173 width=20) (actual time=0.002..1.381 rows=3,173 loops=1)

12. 0.003 0.035 ↑ 1.5 2 1

Hash (cost=14.41..14.41 rows=3 width=36) (actual time=0.034..0.035 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.032 0.032 ↑ 1.5 2 1

Seq Scan on pick_list_values plv_status (cost=0.00..14.41 rows=3 width=36) (actual time=0.006..0.032 rows=2 loops=1)

  • Filter: (pick_list_id = 122)
  • Rows Removed by Filter: 511
14. 0.006 0.061 ↓ 1.3 4 1

Hash (cost=14.41..14.41 rows=3 width=36) (actual time=0.060..0.061 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.055 0.055 ↓ 1.3 4 1

Seq Scan on pick_list_values plv_reason (cost=0.00..14.41 rows=3 width=36) (actual time=0.013..0.055 rows=4 loops=1)

  • Filter: (pick_list_id = 123)
  • Rows Removed by Filter: 509
16. 9.519 9.519 ↑ 1.0 1 3,173

Index Scan using clients_pkey on clients c (cost=0.28..0.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,173)

  • Index Cond: (id = cw.client_id)
17. 12.692 12.692 ↑ 1.0 1 3,173

Index Scan using investment_lists_pkey on investment_lists li (cost=0.42..4.83 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=3,173)

  • Index Cond: (id = cw.investment_id)
18. 5,587.653 24,016.437 ↓ 582.0 1,164 3,173

GroupAggregate (cost=130,675.59..130,675.64 rows=2 width=52) (actual time=5.213..7.569 rows=1,164 loops=3,173)

  • Group Key: li_1.id, c_1.id, ciw.id
19. 1,811.426 18,428.784 ↓ 1,433.0 2,866 3,173

Sort (cost=130,675.59..130,675.59 rows=2 width=64) (actual time=5.211..5.808 rows=2,866 loops=3,173)

  • Sort Key: li_1.id, c_1.id, ciw.id
  • Sort Method: quicksort Memory: 463kB
20. 1,287.096 16,617.358 ↓ 1,433.0 2,866 1

Gather (cost=129,915.60..130,675.58 rows=2 width=64) (actual time=11,768.400..16,617.358 rows=2,866 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 2.335 15,330.262 ↓ 955.0 955 3 / 3

Nested Loop (cost=128,915.60..129,675.38 rows=1 width=64) (actual time=11,840.126..15,330.262 rows=955 loops=3)

  • Join Filter: (ciw.investment_id = li_1.id)
22. 1,468.782 15,324.106 ↑ 1.0 955 3 / 3

Merge Join (cost=128,915.18..129,238.00 rows=957 width=36) (actual time=11,840.091..15,324.106 rows=955 loops=3)

  • Merge Cond: ((pi.investment_list_id = ciw.investment_id) AND (c_1.id = ciw.client_id))
23. 6,472.968 13,845.199 ↓ 57.7 2,226,864 3 / 3

Sort (cost=128,651.91..128,748.40 rows=38,598 width=28) (actual time=11,825.228..13,845.199 rows=2,226,864 loops=3)

  • Sort Key: pi.investment_list_id, c_1.id
  • Sort Method: external merge Disk: 55656kB
  • Worker 0: Sort Method: external merge Disk: 57112kB
  • Worker 1: Sort Method: external merge Disk: 54920kB
24. 5,121.328 7,372.231 ↓ 57.7 2,226,864 3 / 3

Parallel Hash Join (cost=6,267.39..125,711.47 rows=38,598 width=28) (actual time=4,500.694..7,372.231 rows=2,226,864 loops=3)

  • Hash Cond: (pi.plan_id = big_plans.id)
25. 1,585.948 1,585.948 ↑ 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=1.230..1,585.948 rows=2,272,526 loops=3)

26. 154.142 664.955 ↓ 37.6 100,357 3 / 3

Parallel Hash (cost=6,234.00..6,234.00 rows=2,671 width=8) (actual time=664.954..664.955 rows=100,357 loops=3)

  • Buckets: 65536 (originally 8192) Batches: 8 (originally 1) Memory Usage: 2048kB
27. 121.623 510.813 ↓ 37.6 100,357 3 / 3

Nested Loop (cost=1,921.40..6,234.00 rows=2,671 width=8) (actual time=85.257..510.813 rows=100,357 loops=3)

28. 6.896 135.899 ↓ 152.9 5,506 3 / 3

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

  • Hash Cond: (companies.id = c_1.company_id)
29. 11.707 125.853 ↓ 7.9 5,506 3 / 3

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

30. 10.863 97.627 ↓ 7.9 5,506 3 / 3

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

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

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

32. 39.868 81.626 ↑ 1.0 49,650 3 / 3

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

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

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

34. 16.519 16.519 ↑ 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.003..0.003 rows=1 loops=16,519)

  • Index Cond: (id = people.company_id)
  • Heap Fetches: 2494
35. 1.608 3.150 ↓ 4.7 3,023 3 / 3

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

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

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

37. 253.291 253.291 ↑ 4.1 18 16,519 / 3

Index Scan using big_plans__user_id_owner_idx on big_plans (cost=0.42..65.95 rows=73 width=8) (actual time=0.006..0.046 rows=18 loops=16,519)

  • Index Cond: (user_id_owner = users.id)
  • Filter: is_active_client_plan
  • Rows Removed by Filter: 2
38. 8.455 10.125 ↓ 1.1 3,502 3 / 3

Sort (cost=263.27..271.20 rows=3,173 width=12) (actual time=7.084..10.125 rows=3,502 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
39. 1.670 1.670 ↑ 1.0 3,173 3 / 3

Seq Scan on client_investment_watches ciw (cost=0.00..78.73 rows=3,173 width=12) (actual time=0.031..1.670 rows=3,173 loops=3)

40. 3.821 3.821 ↑ 1.0 1 2,866 / 3

Index Scan using investment_lists_pkey on investment_lists li_1 (cost=0.42..0.44 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=2,866)

  • Index Cond: (id = pi.investment_list_id)
Planning time : 12.277 ms
Execution time : 24,988.671 ms