explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PsXk

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 43.379 1,636.009 ↓ 27.3 10,507 1

WindowAgg (cost=19,286.31..19,307.49 rows=385 width=294) (actual time=1,585.630..1,636.009 rows=10,507 loops=1)

2. 35.019 1,592.630 ↓ 27.3 10,507 1

Sort (cost=19,286.31..19,287.27 rows=385 width=173) (actual time=1,584.966..1,592.630 rows=10,507 loops=1)

  • Sort Key: t1.portfolioid, t1.securityid1, t1.lotnumber, t1.tradedate, t1.portfoliotransactionid
  • Sort Method: quicksort Memory: 3121kB
3. 241.839 1,557.611 ↓ 27.3 10,507 1

Nested Loop (cost=12,309.55..19,269.78 rows=385 width=173) (actual time=555.467..1,557.611 rows=10,507 loops=1)

4. 227.112 782.512 ↓ 335.0 133,315 1

Hash Right Join (cost=12,309.26..18,632.32 rows=398 width=141) (actual time=555.416..782.512 rows=133,315 loops=1)

  • Hash Cond: ((t2.securityid1 = t1.securityid1) AND (t2.lotnumber = t1.lotnumber))
5. 0.003 6.597 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.71..6,292.75 rows=31 width=44) (actual time=6.597..6.597 rows=0 loops=1)

6. 6.594 6.594 ↓ 0.0 0 1

Index Scan using idx_porttrns_portid_tradedate on vportfoliotransaction t2 (cost=0.42..6,050.98 rows=31 width=41) (actual time=6.594..6.594 rows=0 loops=1)

  • Index Cond: ((portfolioid = 213) AND (tradedate <= '2014-03-26 00:00:00'::timestamp without time zone))
  • Filter: ((trancodelabel)::text = ANY ('{"Adjust Cost (Long)","Adjust Cost (Short)"}'::text[]))
  • Rows Removed by Filter: 19595
7. 0.000 0.000 ↓ 0.0 0

Index Scan using portfsecuritynew_securityid_idx1 on portfsecurity s2 (cost=0.29..7.79 rows=1 width=7) (never executed)

  • Index Cond: (securityid = t2.securityid2)
8. 162.909 548.803 ↓ 335.0 133,315 1

Hash (cost=12,302.58..12,302.58 rows=398 width=105) (actual time=548.803..548.803 rows=133,315 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 2 (originally 1) Memory Usage: 17594kB
9. 201.999 385.894 ↓ 335.0 133,315 1

Merge Left Join (cost=12,283.46..12,302.58 rows=398 width=105) (actual time=92.555..385.894 rows=133,315 loops=1)

  • Merge Cond: ((pt.securityid1 = t1.securityid1) AND (pt.lotnumber = t1.lotnumber))
10. 18.166 57.932 ↓ 20.2 8,040 1

Sort (cost=6,113.27..6,114.26 rows=398 width=60) (actual time=52.172..57.932 rows=8,040 loops=1)

  • Sort Key: pt.securityid1, pt.lotnumber
  • Sort Method: quicksort Memory: 1013kB
11. 15.714 39.766 ↓ 20.2 8,040 1

Hash Join (cost=28.26..6,096.08 rows=398 width=60) (actual time=0.307..39.766 rows=8,040 loops=1)

  • Hash Cond: ((pt.trancodelabel)::text = (tc.trancodelabel)::text)
12. 23.861 23.861 ↓ 6.7 8,040 1

Index Scan using idx_porttrns_portid_tradedate on vportfoliotransaction pt (cost=0.42..6,059.79 rows=1,193 width=60) (actual time=0.081..23.861 rows=8,040 loops=1)

  • Index Cond: ((portfolioid = 213) AND (tradedate <= '2014-03-26 00:00:00'::timestamp without time zone))
  • Filter: ((trancodelabel)::text = ANY ('{Buy,"Sell Short","Transfer In (Long)","Deliver In (Long)"}'::text[]))
  • Rows Removed by Filter: 11555
13. 0.025 0.191 ↑ 22.3 16 1

Hash (cost=23.38..23.38 rows=357 width=17) (actual time=0.191..0.191 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.166 0.166 ↑ 22.3 16 1

Seq Scan on advtransactioncode tc (cost=0.00..23.38 rows=357 width=17) (actual time=0.023..0.166 rows=16 loops=1)

  • Filter: (effectonquantity <> 0)
  • Rows Removed by Filter: 32
15. 105.811 125.963 ↓ 66.3 133,315 1

Sort (cost=6,170.19..6,175.22 rows=2,012 width=45) (actual time=40.365..125.963 rows=133,315 loops=1)

  • Sort Key: t1.securityid1, t1.lotnumber
  • Sort Method: quicksort Memory: 1781kB
16. 20.152 20.152 ↓ 5.5 11,109 1

Index Scan using idx_porttrns_portid_tradedate on vportfoliotransaction t1 (cost=0.42..6,059.79 rows=2,012 width=45) (actual time=0.036..20.152 rows=11,109 loops=1)

  • Index Cond: ((portfolioid = 213) AND (tradedate <= '2014-03-26 00:00:00'::timestamp without time zone))
  • Filter: ((trancodelabel)::text = ANY ('{Sell,"Cover Short","Deliver Out (Long)","Transfer Out (Long)"}'::text[]))
  • Rows Removed by Filter: 8486
17. 533.260 533.260 ↓ 0.0 0 133,315

Index Scan using portfsecuritynew_securityid_idx1 on portfsecurity s1 (cost=0.29..1.59 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=133,315)

  • Index Cond: (securityid = pt.securityid1)
  • Filter: ((sectypebasecode)::text <> 'ca'::text)
  • Rows Removed by Filter: 1