[GENERAL] query optimization

[Kevin Duffy]

 

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