Re: query optimization

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(at)une(dot)edu(dot)au]
Sent: Thursday, July 17, 2008 7:41 PM
To: Kevin Duffy
Cc: pgsql-general(at)postgresql(dot)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 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.
>

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(at)une(dot)edu(dot)au