query optimization

["Kevin Duffy" <KD@xxxxxxxxxxxxxxxxx>]

 

Hello:

 

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

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