Re: Upgrading to 7.2

Chris Field <cfields(at)affinitysolutions(dot)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