explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MiTj

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 30,225.848 ↑ 1.0 1 1

Aggregate (cost=130,787.97..130,787.98 rows=1 width=8) (actual time=30,225.847..30,225.848 rows=1 loops=1)

2. 1.299 30,225.835 ↓ 0.0 0 1

Subquery Scan on watches (cost=130,787.93..130,787.97 rows=1 width=4) (actual time=30,225.835..30,225.835 rows=0 loops=1)

  • Filter: ((watches.row_number = 1) AND (watches.company_id = 2005384))
  • Rows Removed by Filter: 3383
3. 5.788 30,224.536 ↓ 3,383.0 3,383 1

WindowAgg (cost=130,787.93..130,787.95 rows=1 width=164) (actual time=30,217.510..30,224.536 rows=3,383 loops=1)

4. 5.684 30,218.748 ↓ 3,383.0 3,383 1

Sort (cost=130,787.93..130,787.93 rows=1 width=16) (actual time=30,217.493..30,218.748 rows=3,383 loops=1)

  • Sort Key: li.id, cw.client_id, cw.id DESC
  • Sort Method: quicksort Memory: 255kB
5. 1,409.453 30,213.064 ↓ 3,383.0 3,383 1

Nested Loop Left Join (cost=130,693.26..130,787.92 rows=1 width=16) (actual time=19,231.569..30,213.064 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
6. 6.988 62.577 ↓ 3,173.0 3,173 1

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

7. 6.702 39.724 ↓ 3,173.0 3,173 1

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

8. 3.972 10.811 ↓ 3,173.0 3,173 1

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

  • Hash Cond: (cw.watch_list_reason_picklist_id = plv_reason.value)
9. 4.611 6.740 ↓ 66.1 3,173 1

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

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

11. 0.005 0.079 ↑ 1.5 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.074 0.074 ↑ 1.5 2 1

Seq Scan on pick_list_values plv_status (cost=0.00..14.41 rows=3 width=4) (actual time=0.013..0.074 rows=2 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.091 0.091 ↓ 1.3 4 1

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

  • Filter: (pick_list_id = 123)
  • Rows Removed by Filter: 509
15. 22.211 22.211 ↑ 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.007..0.007 rows=1 loops=3,173)

  • Index Cond: (id = cw.client_id)
16. 15.865 15.865 ↑ 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.005..0.005 rows=1 loops=3,173)

  • Index Cond: (id = cw.investment_id)
  • Heap Fetches: 2657
17. 6,219.080 28,741.034 ↓ 582.0 1,164 3,173

GroupAggregate (cost=130,663.67..130,663.71 rows=2 width=52) (actual time=6.062..9.058 rows=1,164 loops=3,173)

  • Group Key: ciw.id, c_1.id, li_1.id
18. 3,183.240 22,521.954 ↓ 1,433.0 2,866 3,173

Sort (cost=130,663.67..130,663.67 rows=2 width=12) (actual time=6.060..7.098 rows=2,866 loops=3,173)

  • Sort Key: ciw.id, c_1.id, li_1.id
  • Sort Method: quicksort Memory: 231kB
19. 878.368 19,338.714 ↓ 1,433.0 2,866 1

Gather (cost=129,905.24..130,663.66 rows=2 width=12) (actual time=13,990.782..19,338.714 rows=2,866 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 9.481 18,460.346 ↓ 955.0 955 3 / 3

Nested Loop (cost=128,905.24..129,663.46 rows=1 width=12) (actual time=14,317.019..18,460.346 rows=955 loops=3)

  • Join Filter: (ciw.investment_id = li_1.id)
21. 1,802.268 18,450.865 ↑ 1.0 955 3 / 3

Merge Join (cost=128,904.82..129,227.66 rows=957 width=16) (actual time=14,316.976..18,450.865 rows=955 loops=3)

  • Merge Cond: ((pi.investment_list_id = ciw.investment_id) AND (c_1.id = ciw.client_id))
22. 16,648.597 16,648.597 ↓ 57.7 2,226,864 3 / 3

Sort (cost=128,641.55..128,738.05 rows=38,600 width=8) (actual time=14,306.801..16,648.597 rows=2,226,864 loops=3)

  • Sort Key: pi.investment_list_id, c_1.id