explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ppQE

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 0.351 ↑ 60.9 21 1

Nested Loop (cost=446.97..197,319.08 rows=1,278 width=23) (actual time=0.167..0.351 rows=21 loops=1)

2. 0.028 0.268 ↑ 36.8 21 1

Nested Loop (cost=446.39..27,329.25 rows=772 width=16) (actual time=0.156..0.268 rows=21 loops=1)

3. 0.018 0.177 ↑ 108.5 21 1

Nested Loop (cost=445.82..9,513.66 rows=2,279 width=12) (actual time=0.145..0.177 rows=21 loops=1)

4. 0.006 0.129 ↑ 7.7 6 1

HashAggregate (cost=441.00..441.46 rows=46 width=4) (actual time=0.128..0.129 rows=6 loops=1)

  • Group Key: "*SELECT* 1".account_id
5. 0.006 0.123 ↑ 7.7 6 1

GroupAggregate (cost=439.62..440.43 rows=46 width=16) (actual time=0.117..0.123 rows=6 loops=1)

  • Group Key: ""*SELECT* 1"".household_id, ""*SELECT* 1"".account_id
6. 0.020 0.117 ↑ 6.6 7 1

Sort (cost=439.62..439.74 rows=46 width=8) (actual time=0.115..0.117 rows=7 loops=1)

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

Append (cost=0.71..438.35 rows=46 width=8) (actual time=0.040..0.097 rows=7 loops=1)

8. 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)

9. 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)

10. 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
11. 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
12. 0.001 0.073 ↑ 3.3 7 1

Subquery Scan on *SELECT* 2 (cost=0.84..232.34 rows=23 width=8) (actual time=0.019..0.073 rows=7 loops=1)

13. 0.011 0.072 ↑ 3.3 7 1

Nested Loop (cost=0.84..232.11 rows=23 width=16) (actual time=0.019..0.072 rows=7 loops=1)

14. 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.009..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
15. 0.028 0.028 ↑ 2.0 1 7

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

  • Index Cond: (client_id = hc.client_id)
16. 0.012 0.030 ↑ 12.5 4 6

Bitmap Heap Scan on "position" p (cost=4.82..196.72 rows=50 width=12) (actual time=0.004..0.005 rows=4 loops=6)

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

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

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

Index Scan using unique_position_history on position_history ph (cost=0.57..7.81 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))
19. 0.063 0.063 ↑ 393.0 1 21

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

  • Index Cond: (position_history_id = ph.position_history_id)