query optimization

Hello:

This posting is a follow up to this posting from July 15th.

http://archives.postgresql.org/pgsql-general/2008-07/msg00569.php

Given the following select statement

select sec.*

from security sec , positions_gsco

where positions_gsco.securitykey is NULL and

upper(positions_gsco.producttype) = 'OP' and

upper(substring(productid,1,3)) = 'CFD' 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 ) ;

Run as is this statement never returns .

Additional info:

select * from positions_gsco

where ((securitykey IS NULL) AND
(upper("substring"((productid)::text, 1, 3)) = 'CFD'::text) AND

(upper((producttype)::text) = 'OP'::text) AND (taskrunkey =
359))

Returns 2538 rows in 1625ms

select * from security

where (getsectypekey('CFD'::bpchar) = securitytypekey);

returns 2538 rows in 1078ms

so we are not dealing with very large datasets.

Security has an index defined as follows:

CREATE INDEX security_sectypekey ON "security" USING btree
(securitytypekey);

These is a total of 11443 rows in security.

If I change the getsectypekey('CFD') in the above statement to be
either ' (select getsectypekey('CFD') ) ' or '5'

I get 2632 rows in approx 4300ms. There may be an issue here with
number of rows returned. I'll look into that.

But the point is it returns in a reasonable number of seconds.

So here are the questions for the PSQL gurus:

Is getsectypekey('CFD') executing for every join (or possible join)
between positions_gsco and security?

Causing a scan of security for every possible join.

Does ' (select getsectypekey('CFD') ) ' cause the getsectype()
function to be executed once and thus

allowing the index on security to be used.

And of couse '5' makes things simple. The index on security is used.

Am I posting this in the right. If not please help me correct my error
and point me to the correct spot.

Thanks for taking a look at my issue.

Best Regards

Kevin Duffy