explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wlv7

Settings
# exclusive inclusive rows x rows loops node
1. 0.741 2,758.915 ↓ 645.0 645 1

Sort (cost=529.14..529.14 rows=1 width=36) (actual time=2,758.902..2,758.915 rows=645 loops=1)

  • Sort Key: i.f_accountid, i.f_portfolioid, p.posno
  • Sort Method: quicksort Memory: 75kB
2. 3.538 2,758.174 ↓ 645.0 645 1

Nested Loop Left Join (cost=287.60..529.13 rows=1 width=36) (actual time=741.991..2,758.174 rows=645 loops=1)

  • Filter: (COALESCE(bs_1.startdate, $2) <= bs.startdate)
  • Rows Removed by Filter: 2631
3. 8.844 25.728 ↓ 3,276.0 3,276 1

Nested Loop Left Join (cost=5.50..246.99 rows=1 width=46) (actual time=0.402..25.728 rows=3,276 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: 1203
4. 4.182 12.405 ↓ 4,479.0 4,479 1

Hash Join (cost=5.36..246.35 rows=1 width=48) (actual time=0.366..12.405 rows=4,479 loops=1)

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

Hash Join (cost=5.11..245.92 rows=21 width=44) (actual time=0.293..8.174 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.168 1.168 ↑ 1.0 5,693 1

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

7. 0.032 0.099 ↓ 1.0 96 1

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

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

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

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

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

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

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

11. 4.479 4.479 ↓ 0.0 0 4,479

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=4,479)

  • Index Cond: (secid = p.secid)
12. 6.552 2,728.908 ↑ 1.0 1 3,276

Limit (cost=282.10..282.11 rows=1 width=4) (actual time=0.832..0.833 rows=1 loops=3,276)

13. 317.772 2,722.356 ↑ 1.0 1 3,276

Sort (cost=282.10..282.11 rows=1 width=4) (actual time=0.831..0.831 rows=1 loops=3,276)

  • Sort Key: bs_1.startdate DESC
  • Sort Method: top-N heapsort Memory: 25kB
14. 2,404.584 2,404.584 ↓ 893.0 893 3,276

Seq Scan on basicsnapshot bs_1 (cost=0.00..282.09 rows=1 width=4) (actual time=0.231..0.734 rows=893 loops=3,276)

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