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
