explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZG3J : Optimization for: plan #yO6o

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 31,879.595 ↓ 8.0 8 1

Limit (cost=130,800.54..130,800.54 rows=1 width=193) (actual time=31,879.586..31,879.595 rows=8 loops=1)

2. 0.035 31,879.587 ↓ 8.0 8 1

Sort (cost=130,800.54..130,800.54 rows=1 width=193) (actual time=31,879.583..31,879.587 rows=8 loops=1)

  • Sort Key: watches.total_assets DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.013 31,879.552 ↓ 8.0 8 1

Unique (cost=130,800.51..130,800.52 rows=1 width=193) (actual time=31,879.538..31,879.552 rows=8 loops=1)

4. 0.024 31,879.539 ↓ 8.0 8 1

Sort (cost=130,800.51..130,800.52 rows=1 width=193) (actual time=31,879.536..31,879.539 rows=8 loops=1)

  • Sort Key: watches.id
  • Sort Method: quicksort Memory: 26kB
5. 1.292 31,879.515 ↓ 8.0 8 1

Subquery Scan on watches (cost=130,800.46..130,800.50 rows=1 width=193) (actual time=31,872.833..31,879.515 rows=8 loops=1)

  • Filter: ((watches.row_number = 1) AND (watches.company_id = 2032883))
  • Rows Removed by Filter: 3375
6. 6.587 31,878.223 ↓ 3,383.0 3,383 1

WindowAgg (cost=130,800.46..130,800.49 rows=1 width=177) (actual time=31,870.337..31,878.223 rows=3,383 loops=1)

7. 8.026 31,871.636 ↓ 3,383.0 3,383 1

Sort (cost=130,800.46..130,800.47 rows=1 width=169) (actual time=31,870.317..31,871.636 rows=3,383 loops=1)

  • Sort Key: li.id, cw.client_id, cw.id DESC
  • Sort Method: quicksort Memory: 579kB
8. 1,281.261 31,863.610 ↓ 3,383.0 3,383 1

Nested Loop Left Join (cost=130,704.44..130,800.45 rows=1 width=169) (actual time=18,705.183..31,863.610 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
9. 7.312 61.262 ↓ 3,173.0 3,173 1

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

10. 7.423 38.085 ↓ 3,173.0 3,173 1

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

11. 4.197 11.624 ↓ 3,173.0 3,173 1

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

  • Hash Cond: (cw.watch_list_reason_picklist_id = plv_reason.value)
12. 5.021 7.332 ↓ 66.1 3,173 1

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

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

14. 0.005 0.070 ↑ 1.5 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.065 0.065 ↑ 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.065 rows=2 loops=1)

  • Filter: (pick_list_id = 122)
  • Rows Removed by Filter: 511
16. 0.008 0.095 ↓ 1.3 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.087 0.087 ↓ 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.087 rows=4 loops=1)

  • Filter: (pick_list_id = 123)
  • Rows Removed by Filter: 509
18. 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)
19. 15.865 15.865 ↑ 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.005..0.005 rows=1 loops=3,173)

  • Index Cond: (id = cw.investment_id)
20. 8,859.016 30,521.087 ↓ 582.0 1,164 3,173

GroupAggregate (cost=130,674.84..130,674.90 rows=2 width=52) (actual time=5.897..9.619 rows=1,164 loops=3,173)

  • Group Key: li_1.id, c_1.id, ciw.id
21. 21,662.071 21,662.071 ↓ 1,433.0 2,866 3,173

Sort (cost=130,674.84..130,674.85 rows=2 width=64) (actual time=5.894..6.827 rows=2,866 loops=3,173)

  • Sort Key: li_1.id, c_1.id, ciw.id: