query optimization

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