I have a couple wuick questions about upgrading to 7.2. 

First, are there noticable performance improvements with 7.2?  I am
currently using 7.1.2 and it is having a real hard time with large
tables(about 10million records), queries take forever(even simple ones
that are index scans)?

Second, when compiling with gcc, what level of optimization is safe to
use?  I know that some code when it is compiler optimized can break
really badly.

Third, 7.2 will allow for the creation of fuinctional indexes right? In
my current version, it refuses to create them saying that it cannot
create index on max(int4);

And last of all, is 7.2 just a drop in replacement, or is this going to
ba a painful process?


-- 
Chris Field
Affinity Solutions Inc.
14 Feb 2002 11:25:00 -0500
Chris Field <cfields@affinitysolutions.com>
Upgrading to 7.2
Chris Field <cfields@affinitysolutions.com> writes:

> I have a couple wuick questions about upgrading to 7.2. 

I'm not in any sense a guru but I'll try to give some answers...

> First, are there noticable performance improvements with 7.2?  I am
> currently using 7.1.2 and it is having a real hard time with large
> tables(about 10million records), queries take forever(even simple ones
> that are index scans)?

7.2 keeps better statistics, so it will sometimes choose smarter
queries than 7.1--whether this will help you is not clear.  You can
always post your schema and EXPLAIN outout and see if someone can help
speed up the query (best to post those kinds of questions to GENERAL
rather than HACKERS). 

> Second, when compiling with gcc, what level of optimization is safe to
> use?  I know that some code when it is compiler optimized can break
> really badly.

Going with the optimizations that 'configure' picks for you is
probably safe.  Databases are usually I/O bound rather than CPU bound
anyhow. 

> Third, 7.2 will allow for the creation of fuinctional indexes right? In
> my current version, it refuses to create them saying that it cannot
> create index on max(int4);

Ummm, max() is an aggregate function, how can you create an index on
it? 

> And last of all, is 7.2 just a drop in replacement, or is this going to
> ba a painful process?

Should just be a pg_dump, upgrade, initdb, restore process.  There are
a couple gotchas if your DB contains large objects but no
showstoppers.  The dump/restore will probably take a while since your
tables are large...

Personally, I'd install 7.2 in a different place from 7.1 as a test,
and make sure you can restore into 7.2 from your 7.1 dump before doing
the "real" switchover. 

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

14 Feb 2002 11:58:58 -0500
Doug McNaught <doug@wireboard.com>
Re: Upgrading to 7.2
Thanks for responding, I am thinking it might be fairly beneficial to
upgrade.

> Ummm, max() is an aggregate function, how can you create an index on
> it? 

In the postgresSQL Essential Reference by Barry Stinson it specifically
has a index topic on functional indexes, with the given example being
" CREATE INDEX max_payroll_idx ON payroll (MAX(salary)); "
so either the book was a waste of money, or this is a 7.2 specific
feature.

-- 
Chris Field
Affinity Solutions Inc.
14 Feb 2002 12:08:42 -0500
Chris Field <cfields@affinitysolutions.com>
Re: Upgrading to 7.2
Chris Field <cfields@affinitysolutions.com> writes:

> Thanks for responding, I am thinking it might be fairly beneficial to
> upgrade.
> 
> > Ummm, max() is an aggregate function, how can you create an index on
> > it? 
> 
> In the postgresSQL Essential Reference by Barry Stinson it specifically
> has a index topic on functional indexes, with the given example being
> " CREATE INDEX max_payroll_idx ON payroll (MAX(salary)); "

This just seems wrong.  MAX() is a function, not of a single value,
but of a set of values from a single column (ie it's an aggregate
function).  Think about what an index is, and I think you'll see that
you can't build one on based on an aggregate function.  It's not a
well-defined concept.

Think of it this way--an index is "a list of rows, organized by the
value of the index expression for each row."  An aggregate function
like MAX() or SUM() doesn't have a useful value for a single row--it's
only meaningful in the context of a set of rows.

Non-aggregate functions (ie most of them, like sqrt(), sin(), cos()
etc) can definitely be used in indexes.

> so either the book was a waste of money, or this is a 7.2 specific
> feature.

The author does seem confused about this point, but the book still
might be worthwhile--haven't read it myself. 

I might be totally out in left field here, but the reasoning above
makes sense to me at least.  ;)

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

14 Feb 2002 12:27:10 -0500
Doug McNaught <doug@wireboard.com>
Re: Upgrading to 7.2
I have that book too (page 168 in my revision) definitely looks like an 
error. Indexes cannot be applied to aggregate functions since the data 
rowset is only defined in the context of a given query. So, until 
functionality is added to maintain an index based on a SQL query (view 
or what not) rather than a table, this just ain't gonna happen. :)

Chris Field wrote:

> Thanks for responding, I am thinking it might be fairly beneficial to
> upgrade.
> 
> 
>>Ummm, max() is an aggregate function, how can you create an index on
>>it? 
>>
> 
> In the postgresSQL Essential Reference by Barry Stinson it specifically
> has a index topic on functional indexes, with the given example being
> " CREATE INDEX max_payroll_idx ON payroll (MAX(salary)); "
> so either the book was a waste of money, or this is a 7.2 specific
> feature.
> 
> 


-- 
01010101010101010101010101010101010101010101010101

Marc P. Lavergne [wk:650-576-7978 hm:407-648-6996]
Senior Software Developer
Global Knowledge Management
Worldwide Support Technologies
Openwave Systems Inc.

--

"Anyone who slaps a 'this page is best viewed with
Browser X' label on a Web page appears to be
yearning for the bad old days, before the Web,
when you had very little chance of reading a
document written on another computer, another word
processor, or another network."
-Tim Berners-Lee (Technology Review, July 1996)

01010101010101010101010101010101010101010101010101


Thu, 14 Feb 2002 12:50:13 -0500
Marc Lavergne <mlavergne-pub@richlava.com>
Re: Upgrading to 7.2