Thread: Upgrading to 7.2
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.
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
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.
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
On Thu, 2002-02-14 at 12:31, Bruce Momjian wrote: > Chris Field wrote: > > Checking application/pgp-signature: FAILURE > -- Start of PGP signed section. > > 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. > > Uh, MAX() is an aggregate, not really a function like the other > functions. It takes an entire column and returns one value, rather than > normal functions that take a some values and return a single value. > > In summary, I don't think we support aggregates (MAX) in functional > indexes. > So, to put it succinctly book=wrong; -- Chris Field Affinity Solutions Inc.
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