explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8jk6

Settings
# exclusive inclusive rows x rows loops node
1. 1.112 4,131.352 ↓ 1,281.0 1,281 1

Sort (cost=529.14..529.14 rows=1 width=36) (actual time=4,131.326..4,131.352 rows=1,281 loops=1)

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

Nested Loop Left Join (cost=287.60..529.13 rows=1 width=36) (actual time=948.865..4,130.240 rows=1,281 loops=1)

  • Filter: (COALESCE(bs_1.startdate, $2) <= bs.startdate)
  • Rows Removed by Filter: 3196
3. 12.896 45.958 ↓ 4,477.0 4,477 1

Nested Loop Left Join (cost=5.50..246.99 rows=1 width=46) (actual time=0.122..45.958 rows=4,477 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: 1216
4. 7.710 21.676 ↓ 5,693.0 5,693 1

Hash Join (cost=5.36..246.35 rows=1 width=48) (actual time=0.103..21.676 rows=5,693 loops=1)

  • Hash Cond: ((p.accid = i.f_accountid) AND (p.portfolioid = i.f_portfolioid))
5. 12.167 13.957 ↓ 271.1 5,693 1

Hash Join (cost=5.11..245.92 rows=21 width=44) (actual time=0.085..13.957 rows=5,693 loops=1)

  • Hash Cond: ((bs.accid = p.accid) AND (bs.posno = p.posno))
  • Join Filter: ((bs.proposed * p.hasproposed) = bs.proposed)
6. 1.726 1.726 ↑ 1.0 5,693 1

Seq Scan on basicsnapshot bs (cost=0.00..210.93 rows=5,693 width=30) (actual time=0.013..1.726 rows=5,693 loops=1)

7. 0.016 0.064 ↓ 1.0 96 1

Hash (cost=3.68..3.68 rows=95 width=20) (actual time=0.064..0.064 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
8. 0.048 0.048 ↓ 1.0 96 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.006 0.006 ↑ 5.0 2 1

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

11. 11.386 11.386 ↓ 0.0 0 5,693

Index Scan using pk_security_1 on securitymain sm (cost=0.14..0.62 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=5,693)

  • Index Cond: (secid = p.secid)
12. 8.954 4,074.070 ↑ 1.0 1 4,477

Limit (cost=282.10..282.11 rows=1 width=4) (actual time=0.910..0.910 rows=1 loops=4,477)

13. 407.407 4,065.116 ↑ 1.0 1 4,477

Sort (cost=282.10..282.11 rows=1 width=4) (actual time=0.908..0.908 rows=1 loops=4,477)

  • Sort Key: bs_1.startdate DESC
  • Sort Method: top-N heapsort Memory: 25kB
14. 3,657.709 3,657.709 ↓ 805.0 805 4,477

Seq Scan on basicsnapshot bs_1 (cost=0.00..282.09 rows=1 width=4) (actual time=0.349..0.817 rows=805 loops=4,477)

  • Filter: ((startdate <= $2) AND (accid = p.accid) AND (posno = p.posno) AND (proposed = (proposed * p.hasproposed)))
  • Rows Removed by Filter: 4888