explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OY5G

Settings
# exclusive inclusive rows x rows loops node
1. 0.097 41,097.738 ↑ 1.0 1 1

Aggregate (cost=130,786.59..130,786.60 rows=1 width=8) (actual time=41,097.737..41,097.738 rows=1 loops=1)

2. 10.924 41,097.641 ↓ 0.0 0 1

Subquery Scan on vw_watch_list_aggregates (cost=130,786.52..130,786.58 rows=1 width=8) (actual time=41,097.641..41,097.641 rows=0 loops=1)

  • Filter: (vw_watch_list_aggregates.company_id = 2,005,384)
  • Rows Removed by Filter: 3,003
3. 27.777 41,086.717 ↓ 3,003.0 3,003 1

WindowAgg (cost=130,786.52..130,786.57 rows=1 width=209) (actual time=41,018.022..41,086.717 rows=3,003 loops=1)

4. 2.564 41,058.940 ↓ 3,003.0 3,003 1

Subquery Scan on watches (cost=130,786.52..130,786.56 rows=1 width=5) (actual time=41,018.014..41,058.940 rows=3,003 loops=1)

  • Filter: (watches.row_number = 1)
  • Rows Removed by Filter: 380
5. 32.216 41,056.376 ↓ 3,383.0 3,383 1

WindowAgg (cost=130,786.52..130,786.54 rows=1 width=220) (actual time=41,018.011..41,056.376 rows=3,383 loops=1)

6. 21.928 41,024.160 ↓ 3,383.0 3,383 1

Sort (cost=130,786.52..130,786.52 rows=1 width=16) (actual time=41,017.994..41,024.160 rows=3,383 loops=1)

  • Sort Key: li.id, cw.client_id, cw.id DESC
  • Sort Method: quicksort Memory: 255kB
7. 2,737.850 41,002.232 ↓ 3,383.0 3,383 1

Nested Loop Left Join (cost=130,691.85..130,786.51 rows=1 width=16) (actual time=20,004.793..41,002.232 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: 3,691,998
8. 5.280 166.171 ↓ 3,173.0 3,173 1

Nested Loop (cost=29.60..124.16 rows=1 width=20) (actual time=0.269..166.171 rows=3,173 loops=1)

9. 26.786 125.988 ↓ 3,173.0 3,173 1

Nested Loop (cost=29.18..120.67 rows=1 width=16) (actual time=0.230..125.988 rows=3,173 loops=1)

10. 8.620 32.569 ↓ 3,173.0 3,173 1

Hash Join (cost=28.90..120.20 rows=1 width=12) (actual time=0.212..32.569 rows=3,173 loops=1)

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

Hash Join (cost=14.45..105.56 rows=48 width=16) (actual time=0.093..23.855 rows=3,173 loops=1)

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

13. 0.005 0.072 ↑ 1.5 2 1

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

  • Buckets: 1,024 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=4) (actual time=0.012..0.067 rows=2 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.086 0.086 ↓ 1.3 4 1

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

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

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

Index Only Scan using investment_lists_pkey on investment_lists li (cost=0.42..3.49 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=3,173)

  • Index Cond: (id = cw.investment_id)
  • Heap Fetches: 2,657
19. 11,638.564 38,098.211 ↓ 582.0 1,164 3,173

GroupAggregate (cost=130,662.26..130,662.30 rows=2 width=52) (actual time=6.306..12.007 rows=1,164 loops=3,173)

  • Group Key: ciw.id, c_1.id, li_1.id
20. 5,698.640 26,459.647 ↓ 1,433.0 2,866 3,173

Sort (cost=130,662.26..130,662.26 rows=2 width=12) (actual time=6.304..8.339 rows=2,866 loops=3,173)

  • Sort Key: ciw.id, c_1.id, li_1.id
  • Sort Method: quicksort Memory: 231kB
21. 1,047.020 20,761.007 ↓ 1,433.0 2,866 1

Gather (cost=129,903.84..130,662.25 rows=2 width=12) (actual time=15,152.055..20,761.007 rows=2,866 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 19,713.987 19,713.987 ↓ 955.0 955 3 / 3

Nested Loop (cost=128,903.84..129,662.05 rows=1 width=12) (actual time=15,024.752..19,713.987 rows=955 loops=3)

  • Join Filter: (ciw.investment_id = li_1.id):