explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OJ5t

Settings
# exclusive inclusive rows x rows loops node
1. 0.315 2.911 ↓ 1,281.0 1,281 1

Sort (cost=56.96..56.96 rows=1 width=36) (actual time=2.877..2.911 rows=1,281 loops=1)

  • Sort Key: i.f_accountid, i.f_portfolioid, p.posno
  • Sort Method: quicksort Memory: 149kB
2. 0.358 2.596 ↓ 1,281.0 1,281 1

Nested Loop (cost=37.92..56.95 rows=1 width=36) (actual time=0.617..2.596 rows=1,281 loops=1)

  • Join Filter: ((COALESCE(bs_1.startdate, $2) <= bs.startdate) AND (i.f_accountid = bs.accid))
  • Rows Removed by Join Filter: 3196
3. 0.004 1.468 ↓ 5.0 5 1

Nested Loop Left Join (cost=37.64..48.40 rows=1 width=30) (actual time=0.426..1.468 rows=5 loops=1)

4. 0.058 0.139 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.39..11.13 rows=1 width=26) (actual time=0.043..0.139 rows=5 loops=1)

  • Filter: ((p.iscash <> 0) OR (480 = COALESCE(sm.sectype, CASE WHEN ((p.secid >= $3) AND (p.secid <= $4)) THEN 480 ELSE '-1'::integer END)))
  • Rows Removed by Filter: 91
5. 0.042 0.081 ↓ 9.6 96 1

Hash Join (cost=0.25..4.75 rows=10 width=28) (actual time=0.031..0.081 rows=96 loops=1)

  • Hash Cond: ((p.accid = i.f_accountid) AND (p.portfolioid = i.f_portfolioid))
6. 0.029 0.029 ↓ 1.0 96 1

Seq Scan on "position" p (cost=0.00..3.68 rows=95 width=20) (actual time=0.011..0.029 rows=96 loops=1)

  • Filter: (0 <> CASE WHEN (($7 & flags) = $7) THEN 1 ELSE 0 END)
7. 0.003 0.010 ↑ 5.0 2 1

Hash (cost=0.10..0.10 rows=10 width=8) (actual time=0.010..0.010 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.007 0.007 ↑ 5.0 2 1

Function Scan on unnest i (cost=0.00..0.10 rows=10 width=8) (actual time=0.007..0.007 rows=2 loops=1)

9. 0.000 0.000 ↓ 0.0 0 96

Index Scan using pk_security_1 on securitymain sm (cost=0.14..0.62 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=96)

  • Index Cond: (secid = p.secid)
10. 0.005 1.325 ↑ 1.0 1 5

Limit (cost=37.25..37.25 rows=1 width=4) (actual time=0.264..0.265 rows=1 loops=5)

11. 0.335 1.320 ↑ 1.0 1 5

Sort (cost=37.25..37.25 rows=1 width=4) (actual time=0.264..0.264 rows=1 loops=5)

  • Sort Key: bs_1.startdate DESC
  • Sort Method: top-N heapsort Memory: 25kB
12. 0.535 0.985 ↓ 640.0 640 5

Bitmap Heap Scan on basicsnapshot bs_1 (cost=4.66..37.24 rows=1 width=4) (actual time=0.095..0.197 rows=640 loops=5)

  • Recheck Cond: ((accid = p.accid) AND (posno = p.posno) AND (startdate <= $2))
  • Filter: (proposed = (proposed * p.hasproposed))
  • Heap Blocks: exact=92
13. 0.450 0.450 ↓ 64.0 640 5

Bitmap Index Scan on idx_basicsnapshot_clustered_2 (cost=0.00..4.66 rows=10 width=0) (actual time=0.090..0.090 rows=640 loops=5)

  • Index Cond: ((accid = p.accid) AND (posno = p.posno) AND (startdate <= $2))
14. 0.770 0.770 ↓ 895.0 895 5

Index Scan using idx_basicsnapshot_clustered_2 on basicsnapshot bs (cost=0.28..8.53 rows=1 width=30) (actual time=0.005..0.154 rows=895 loops=5)

  • Index Cond: ((accid = p.accid) AND (posno = p.posno))
  • Filter: ((proposed * p.hasproposed) = proposed)