Search Postgresql Archives

Re: query optimization

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx] 
Sent: Thursday, July 17, 2008 7:41 PM
To: Kevin Duffy
Cc: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxx



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux