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
Kevin Duffy wrote:
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 thusallowing 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.
Is the function stable or volatile?
As Scott Marlowe suggested, you need to look at the explain results to
find out what the plan is in each case.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
I sent this follow up in yesterday, but it did not show up.
Must be doing something wrong. Here is the second try.
kd
select * from security sec
where getsectypekey('OP') = sec.securitytypekey returns 690 rows
in 1625ms EXPLAIN "Seq Scan on "security" sec (cost=0.00..507.54
rows=602 width=374)"
" Filter: (getsectypekey('OP'::bpchar) = securitytypekey)"
select * from security sec
where ( select getsectypekey('OP') ) = sec.securitytypekey
returns 690 rows in 172ms
EXPLAIN
"Bitmap Heap Scan on "security" sec (cost=16.93..368.36 rows=602
width=374)"
" Recheck Cond: ($0 = securitytypekey)"
" InitPlan"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Bitmap Index Scan on security_sectypekey (cost=0.00..16.77
rows=602 width=0)"
" Index Cond: ($0 = securitytypekey)"
So this proves it is using the index.
But I think the issue is in the interaction between the numbers of rows
returned from positions_gsco and security.
kd
-----Original Message-----
From: Klint Gore [mailto:kgore4@une.edu.au]
Sent: Thursday, July 17, 2008 7:41 PM
To: Kevin Duffy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] query optimization
Kevin Duffy wrote:
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 thusallowing 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.
Is the function stable or volatile?
As Scott Marlowe suggested, you need to look at the explain results to
find out what the plan is in each case.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au