explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cCc

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 1.496 ↓ 21.0 21 1

Unique (cost=203,389.74..203,389.78 rows=1 width=249) (actual time=1.461..1.496 rows=21 loops=1)

2.          

CTE tax_lot_data

3. 0.040 0.443 ↑ 59.4 21 1

GroupAggregate (cost=192,796.18..192,833.62 rows=1,248 width=76) (actual time=0.407..0.443 rows=21 loops=1)

  • Group Key: p.account_id, p.security_id
4. 0.033 0.403 ↑ 59.4 21 1

Sort (cost=192,796.18..192,799.30 rows=1,248 width=23) (actual time=0.394..0.403 rows=21 loops=1)

  • Sort Key: p.account_id, p.security_id
  • Sort Method: quicksort Memory: 26kB
5. 0.026 0.370 ↑ 59.4 21 1

Nested Loop (cost=457.20..192,732.00 rows=1,248 width=23) (actual time=0.168..0.370 rows=21 loops=1)

6. 0.034 0.281 ↑ 36.0 21 1

Nested Loop (cost=456.62..28,171.88 rows=757 width=16) (actual time=0.158..0.281 rows=21 loops=1)

7. 0.019 0.184 ↑ 112.4 21 1

Nested Loop (cost=456.05..9,731.81 rows=2,360 width=12) (actual time=0.146..0.184 rows=21 loops=1)

8. 0.007 0.135 ↑ 8.0 6 1

HashAggregate (cost=451.24..451.72 rows=48 width=4) (actual time=0.133..0.135 rows=6 loops=1)

  • Group Key: "*SELECT* 1".account_id
9. 0.005 0.128 ↑ 8.0 6 1

GroupAggregate (cost=449.80..450.64 rows=48 width=16) (actual time=0.122..0.128 rows=6 loops=1)

  • Group Key: "*SELECT* 1".household_id, "*SELECT* 1".account_id
10. 0.018 0.123 ↑ 6.9 7 1

Sort (cost=449.80..449.92 rows=48 width=8) (actual time=0.120..0.123 rows=7 loops=1)

  • Sort Key: "*SELECT* 1".household_id, "*SELECT* 1".account_id
  • Sort Method: quicksort Memory: 25kB
11. 0.003 0.105 ↑ 6.9 7 1

Append (cost=0.71..448.46 rows=48 width=8) (actual time=0.044..0.105 rows=7 loops=1)

12. 0.001 0.021 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.71..206.01 rows=23 width=8) (actual time=0.021..0.021 rows=0 loops=1)

13. 0.001 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.71..205.78 rows=23 width=16) (actual time=0.020..0.020 rows=0 loops=1)

14. 0.019 0.019 ↓ 0.0 0 1

Index Only Scan using household_account_household_id_account_id_join_date_key on household_account (cost=0.29..103.49 rows=23 width=8) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (household_id = ANY ('{606355,606357,606170,605679,606363,606348,524061,593636,604397,523883,524067}'::integer[]))
  • Heap Fetches: 0
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using account_pkey on account (cost=0.42..4.44 rows=1 width=4) (never executed)

  • Index Cond: (account_id = household_account.account_id)
  • Heap Fetches: 0
16. 0.006 0.081 ↑ 3.6 7 1

Subquery Scan on *SELECT* 2 (cost=0.84..242.45 rows=25 width=8) (actual time=0.023..0.081 rows=7 loops=1)

17. 0.007 0.075 ↑ 3.6 7 1

Nested Loop (cost=0.84..242.20 rows=25 width=16) (actual time=0.021..0.075 rows=7 loops=1)

18. 0.033 0.033 ↑ 2.0 7 1

Index Only Scan using household_client_household_id_client_id_join_date_key on household_client hc (cost=0.42..68.31 rows=14 width=8) (actual time=0.010..0.033 rows=7 loops=1)

  • Index Cond: (household_id = ANY ('{606355,606357,606170,605679,606363,606348,524061,593636,604397,523883,524067}'::integer[]))
  • Heap Fetches: 6
19. 0.035 0.035 ↑ 2.0 1 7

Index Scan using account_client_id_idx on account acc (cost=0.42..12.40 rows=2 width=8) (actual time=0.004..0.005 rows=1 loops=7)

  • Index Cond: (client_id = hc.client_id)
20. 0.012 0.030 ↑ 12.2 4 6

Bitmap Heap Scan on "position" p (cost=4.81..192.85 rows=49 width=12) (actual time=0.004..0.005 rows=4 loops=6)

  • Recheck Cond: (account_id = "*SELECT* 1".account_id)
  • Heap Blocks: exact=1
21. 0.018 0.018 ↑ 12.2 4 6

Bitmap Index Scan on unique_position (cost=0.00..4.80 rows=49 width=0) (actual time=0.003..0.003 rows=4 loops=6)

  • Index Cond: (account_id = "*SELECT* 1".account_id)
22. 0.063 0.063 ↑ 1.0 1 21

Index Scan using unique_position_history on position_history ph (cost=0.57..7.80 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=21)

  • Index Cond: ((position_id = p.position_id) AND (date = '2019-01-17'::date))
23. 0.063 0.063 ↑ 388.0 1 21

Index Scan using tax_lot_history_position_history_idx on tax_lot_history tlh (cost=0.57..213.50 rows=388 width=19) (actual time=0.003..0.003 rows=1 loops=21)

  • Index Cond: (position_history_id = ph.position_history_id)
24. 0.055 1.469 ↓ 21.0 21 1

Sort (cost=10,556.12..10,556.13 rows=1 width=249) (actual time=1.460..1.469 rows=21 loops=1)

  • Sort Key: a.custodian_id, a.account_number, a.firm_id, aggr.account_id, aggr.security_id, s.type, s.description, s.symbol, aggr.sum_quantity, aggr.sum_cost_basis, tld1.sum_cost_basis, tld1.sum_quantity, tld2.sum_cost_basis, tld2.sum_quantity
  • Sort Method: quicksort Memory: 29kB
25. 0.035 1.414 ↓ 21.0 21 1

Nested Loop Left Join (cost=2.43..10,556.11 rows=1 width=249) (actual time=0.503..1.414 rows=21 loops=1)

  • Join Filter: ((ps.account_id = tld2.account_id) AND (ps.security_id = tld2.security_id))
26. 0.185 1.274 ↓ 21.0 21 1

Nested Loop Left Join (cost=2.43..10,518.67 rows=1 width=193) (actual time=0.450..1.274 rows=21 loops=1)

  • Join Filter: ((ps.account_id = tld1.account_id) AND (ps.security_id = tld1.security_id))
  • Rows Removed by Join Filter: 420
27. 0.039 0.900 ↓ 21.0 21 1

Nested Loop (cost=2.43..10,481.23 rows=1 width=129) (actual time=0.440..0.900 rows=21 loops=1)

28. 0.025 0.819 ↓ 21.0 21 1

Nested Loop (cost=2.01..10,473.71 rows=1 width=112) (actual time=0.434..0.819 rows=21 loops=1)

29. 0.023 0.731 ↓ 21.0 21 1

Nested Loop (cost=1.44..10,455.17 rows=1 width=120) (actual time=0.427..0.731 rows=21 loops=1)

30. 0.044 0.645 ↓ 21.0 21 1

Nested Loop (cost=0.86..10,447.35 rows=1 width=112) (actual time=0.422..0.645 rows=21 loops=1)

31. 0.034 0.496 ↓ 2.6 21 1

Nested Loop (cost=0.43..10,387.04 rows=8 width=84) (actual time=0.413..0.496 rows=21 loops=1)

32. 0.420 0.420 ↑ 59.4 21 1

CTE Scan on tax_lot_data aggr (cost=0.00..24.96 rows=1,248 width=72) (actual time=0.409..0.420 rows=21 loops=1)

33. 0.042 0.042 ↑ 1.0 1 21

Index Scan using unique_position on "position" ps (cost=0.43..8.29 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=21)

  • Index Cond: ((account_id = aggr.account_id) AND (security_id = aggr.security_id))
34. 0.105 0.105 ↑ 1.0 1 21

Index Scan using security_pkey on security s (cost=0.43..7.53 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=21)

  • Index Cond: (id = ps.security_id)
35. 0.063 0.063 ↑ 1.0 1 21

Index Scan using unique_position_history on position_history (cost=0.57..7.81 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=21)

  • Index Cond: ((position_id = ps.position_id) AND (date = '2019-01-17'::date))
36. 0.063 0.063 ↑ 388.0 1 21

Index Only Scan using tax_lot_history_position_history_idx on tax_lot_history (cost=0.57..14.66 rows=388 width=4) (actual time=0.002..0.003 rows=1 loops=21)

  • Index Cond: (position_history_id = position_history.position_history_id)
  • Heap Fetches: 0
37. 0.042 0.042 ↑ 1.0 1 21

Index Scan using account_pkey on account a (cost=0.42..7.51 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=21)

  • Index Cond: (account_id = ps.account_id)
38. 0.189 0.189 ↑ 19.8 21 21

CTE Scan on tax_lot_data tld1 (cost=0.00..31.20 rows=416 width=72) (actual time=0.001..0.009 rows=21 loops=21)

  • Filter: (('2019-01-17'::date - purchase_date) <= 365)
39. 0.105 0.105 ↓ 0.0 0 21

CTE Scan on tax_lot_data tld2 (cost=0.00..31.20 rows=416 width=72) (actual time=0.005..0.005 rows=0 loops=21)

  • Filter: (('2019-01-17'::date - purchase_date) > 365)
  • Rows Removed by Filter: 21