explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.037 34,599.925 ↓ 8.0 8 1

Sort (cost=130,801.58..130,801.59 rows=1 width=193) (actual time=34,599.921..34,599.925 rows=8 loops=1)

  • Sort Key: watches.total_assets
  • Sort Method: quicksort Memory: 26kB
2. 0.010 34,599.888 ↓ 8.0 8 1

Unique (cost=130,801.55..130,801.56 rows=1 width=193) (actual time=34,599.876..34,599.888 rows=8 loops=1)

3. 0.027 34,599.878 ↓ 8.0 8 1

Sort (cost=130,801.55..130,801.56 rows=1 width=193) (actual time=34,599.874..34,599.878 rows=8 loops=1)

  • Sort Key: watches.id
  • Sort Method: quicksort Memory: 26kB
4. 1.283 34,599.851 ↓ 8.0 8 1

Subquery Scan on watches (cost=130,801.50..130,801.54 rows=1 width=193) (actual time=34,585.234..34,599.851 rows=8 loops=1)

  • Filter: ((watches.row_number = 1) AND (watches.company_id = 2032883))
  • Rows Removed by Filter: 3375
5. 10.500 34,598.568 ↓ 3,383.0 3,383 1

WindowAgg (cost=130,801.50..130,801.53 rows=1 width=177) (actual time=34,582.771..34,598.568 rows=3,383 loops=1)

6. 15.950 34,588.068 ↓ 3,383.0 3,383 1

Sort (cost=130,801.50..130,801.51 rows=1 width=169) (actual time=34,582.752..34,588.068 rows=3,383 loops=1)

  • Sort Key: li.id, cw.client_id, cw.id DESC
  • Sort Method: quicksort Memory: 579kB
7. 1,386.659 34,572.118 ↓ 3,383.0 3,383 1

Nested Loop Left Join (cost=130,705.49..130,801.49 rows=1 width=169) (actual time=20,269.033..34,572.118 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
8. 6.810 68.858 ↓ 3,173.0 3,173 1

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

9. 8.152 43.010 ↓ 3,173.0 3,173 1

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

10. 4.441 15.820 ↓ 3,173.0 3,173 1

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

  • Hash Cond: (cw.watch_list_reason_picklist_id = plv_reason.value)
11. 9.072 11.279 ↓ 66.1 3,173 1

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

  • Hash Cond: (cw.watch_list_status_picklist_id = plv_status.value)
12. 2.136 2.136 ↑ 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.003..2.136 rows=3,173 loops=1)

13. 0.004 0.071 ↑ 1.5 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.067 0.067 ↑ 1.5 2 1

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

  • Filter: (pick_list_id = 122)
  • Rows Removed by Filter: 511
15. 0.010 0.100 ↓ 1.3 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.090 0.090 ↓ 1.3 4 1

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

  • Filter: (pick_list_id = 123)
  • Rows Removed by Filter: 509
17. 19.038 19.038 ↑ 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.006..0.006 rows=1 loops=3,173)

  • Index Cond: (id = cw.client_id)
18. 19.038 19.038 ↑ 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.006..0.006 rows=1 loops=3,173)

  • Index Cond: (id = cw.investment_id)
19. 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
20. 23,524.622 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