query optimization

["Kevin Duffy" <KD@xxxxxxxxxxxxxxxxx>]

Hello:

 

I need your kind assistance to debug an optimization issue.

 

The following two select statements in my book are almost identical.  One does a lookup for security type ‘CFD’ and

the other does the same lookup except for security ‘OP’.  When run with ‘CFD’  the query never returns.

When run with ‘OP’ the results return almost instantly.  If I hard code ‘CFD’ to be 5 which is the securitytypekey,

the query runs instantly.

 

 

The code for getsectypekey() is below.  Please note SECURITYTYPE contains only 28 rows.

 

Why would these queries run so differently?

 

Many thanks for taking the time to look at this issue.

 

KD

 

 

 

 

select sec.*

  from security sec  ,   positions_gsco

  where  positions_gsco.securitykey is NULL  and

         upper(substring(productid,1,3))  =  'CFD'  and   

         upper(positions_gsco.producttype)  =   'OP'  and 

          getsectypekey('CFD') = sec.securitytypekey  and  

        positions_gsco.taskrunkey  =  359  and 

        positions_gsco.issuecurrency = sec.securityissuecurrISO  and 

        positions_gsco.strikeprice  =  sec.strikeprice  and

        positions_gsco.expirationdate  =  sec.expirationdate  and

      (  positions_gsco.underlyingisin =  sec.underlyingisin  or   

             positions_gsco.underlyingcusip  =  sec.underlyingcusip   or

         positions_gsco.underlyingbloombergticker = sec. underlyingbloomberg )   ;

 

EXPLAIN

"Nested Loop  (cost=0.00..2829.87 rows=1 width=374)"

"  Join Filter: ((positions_gsco.issuecurrency = (sec.securityissuecurriso)::bpchar) AND (positions_gsco.strikeprice = sec.strikeprice) AND (positions_gsco.expirationdate = sec.expirationdate) AND ((positions_gsco.underlyingisin = (sec.underlyingisin)::bpchar) OR (positions_gsco.underlyingcusip = (sec.underlyingcusip)::bpchar) OR (positions_gsco.underlyingbloombergticker = sec.underlyingbloomberg)))"

"  ->  Seq Scan on positions_gsco  (cost=0.00..2310.40 rows=1 width=72)"

"        Filter: ((securitykey IS NULL) AND (upper("substring"((productid)::text, 1, 3)) = 'CFD'::text) AND (upper((producttype)::text) = 'OP'::text) AND (taskrunkey = 359))"

"  ->  Seq Scan on "security" sec  (cost=0.00..504.52 rows=598 width=374)"

"        Filter: (getsectypekey('CFD'::bpchar) = securitytypekey)"

 

 

** ** ** ** ** ** ** 

** ** ** ** ** ** ** 

 

select sec.*

  from security sec  , positions_gsco

 where  positions_gsco.securitykey is NULL  and 

        upper(substring(productid,1,3))  !=  'CFD'  and    

        upper(positions_gsco.producttype)  =   'OP'  and  

        getsectypekey('OP') = sec.securitytypekey  and  

        positions_gsco.taskrunkey  =   359   and

        positions_gsco.issuecurrency = sec.securityissuecurrISO  and 

        positions_gsco.putcallind  =  sec.put_call  and 

        positions_gsco.strikeprice  =  sec.strikeprice  and

        positions_gsco.expirationdate  =  sec.expirationdate  and 

       (  positions_gsco.underlyingisin =  sec.underlyingisin  or    

          positions_gsco.underlyingcusip  =  sec.underlyingcusip or 

          positions_gsco.underlyingbloombergticker  =  sec.underlyingbloomberg    )    ;

 

EXPLAIN

"Hash Join  (cost=514.99..2861.41 rows=1 width=374)"

"  Hash Cond: ((positions_gsco.issuecurrency = (sec.securityissuecurriso)::bpchar) AND (positions_gsco.putcallind = sec.put_call) AND (positions_gsco.expirationdate = sec.expirationdate))"

"  Join Filter: ((positions_gsco.strikeprice = sec.strikeprice) AND ((positions_gsco.underlyingisin = (sec.underlyingisin)::bpchar) OR (positions_gsco.underlyingcusip = (sec.underlyingcusip)::bpchar) OR (positions_gsco.underlyingbloombergticker = sec.underlyingbloomberg)))"

"  ->  Seq Scan on positions_gsco  (cost=0.00..2310.40 rows=16 width=79)"

"        Filter: ((securitykey IS NULL) AND (upper("substring"((productid)::text, 1, 3)) <> 'CFD'::text) AND (upper((producttype)::text) = 'OP'::text) AND (taskrunkey = 359))"

"  ->  Hash  (cost=504.52..504.52 rows=598 width=374)"

"        ->  Seq Scan on "security" sec  (cost=0.00..504.52 rows=598 width=374)"

"              Filter: (getsectypekey('OP'::bpchar) = securitytypekey)"

 

 

** ** ** ** ** ** ** 

** ** ** ** ** ** ** 

 

CREATE OR REPLACE FUNCTION getsectypekey(sectype_in bpchar)

  RETURNS integer AS

$BODY$

declare

    sectypekey  integer;

 

begin

select securitytypekey  into sectypekey 

    from securitytype

    where position (upper('|' || sectype_in  || '|' ) in upper(securitytypeaka) ) > 0;

--

--  did not find a type above

if sectypekey is NULL then

  select securitytypekey  into sectypekey 

    from securitytype

    where upper(securitytypeshort) = 'UNKNOWN';

end if;  

 

return sectypekey;

 

end

$BODY$

  LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION getsectypekey(sectype_in bpchar) OWNER TO postgres;

 

 

Kevin Duffy