explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ph7e

Settings
# exclusive inclusive rows x rows loops node
1. 0.469 5.005 ↓ 1,281.0 1,281 1

Sort (cost=29.89..29.89 rows=1 width=36) (actual time=4.945..5.005 rows=1,281 loops=1)

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

Nested Loop (cost=11.89..29.88 rows=1 width=36) (actual time=0.928..4.536 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.007 2.461 ↓ 5.0 5 1

Nested Loop Left Join (cost=11.61..22.37 rows=1 width=30) (actual time=0.538..2.461 rows=5 loops=1)

4. 0.007 0.224 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.39..11.13 rows=1 width=26) (actual time=0.045..0.224 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.066 0.121 ↓ 9.6 96 1

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

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

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

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

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

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

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

9. 0.096 0.096 ↓ 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.001..0.001 rows=0 loops=96)

  • Index Cond: (secid = p.secid)
10. 0.010 2.230 ↑ 1.0 1 5

Limit (cost=11.22..11.22 rows=1 width=4) (actual time=0.445..0.446 rows=1 loops=5)

11. 0.395 2.220 ↑ 1.0 1 5

Sort (cost=11.22..11.22 rows=1 width=4) (actual time=0.444..0.444 rows=1 loops=5)

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

Index Scan using idx_basicsnapshot_posno_1 on basicsnapshot bs_1 (cost=0.28..11.21 rows=1 width=4) (actual time=0.017..0.365 rows=640 loops=5)

  • Index Cond: (posno = p.posno)
  • Filter: ((startdate <= $2) AND (accid = p.accid) AND (proposed = (proposed * p.hasproposed)))
  • Rows Removed by Filter: 255
13. 1.430 1.430 ↓ 895.0 895 5

Index Scan using idx_basicsnapshot_posno_1 on basicsnapshot bs (cost=0.28..7.49 rows=1 width=30) (actual time=0.008..0.286 rows=895 loops=5)

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