Andy wrote:
Are there any significant performance improvements or regressions from 8.4 to 9.0? If yes, which areas (inserts, updates, selects, etc) are those in?
There were two major rounds of tinkering to the query planner/optimizer that can impact the types of plans you get from some SELECT statements. Join removal allows pulling out tables that used to be involved in a query in earlier versions. That shouldn't ever cause a regression, but since it will cause different types of plans one is still possible. The other changes I'm seeing impact plans relate to increased use of "Materialize" nodes in some types of queries. Those are also normally positive too, but like any plan change there's always a chance for a different plan to turn out to be inferior. There are some other query optimizer changes too, stuff that impacts like NULL handling and comparisons when you're at the end of the range of the previously analyzed segment of the table. But there really wasn't anything changed that will impact INSERT/UPDATE statements much that I'm aware of, or even simple SELECT statements that don't happen to intersect with one of the improved areas. Some earlier versions of PostgreSQL had pretty sweeping performance changes to them; 9.0 has some useful targeted areas, particularly for complicated query plans, but not really general across the board improvements. See the "Performance" section of http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0 for some good examples of most of what I mentioned above, along with some other improvements like finer control over statistics and database query parameters. Better performance from 9.0 is more likely to come from things like scaling out reads using multiple slaves, replicating with the Streaming Replication/Hot Standby combination.
Note that if you turn on some of the new replication features, that can disable some write optimizations and slow things down in a slightly different way than it did before on the master in the process. Specifically, if you touch the new wal_level parameter, some things that used to skip write-ahead log writing will no longer be able to do so. But that situation isn't that much different from earlier versions, where turning on archive_mode and setting the archive_command introduced many of the same de-optimizations.
In a related question, is there any public data that compares the performances of various Postgresql versions?
http://suckit.blog.hu/2009/09/29/postgresql_history covers 8.0 through 8.4, which were the versions that showed the biggest percentage changes upward. The minor regression you see in 8.4 there is mainly due to a change to the default value of default_statistics_target, which was optimized out of the box more for larger queries than tiny ones in that version. That hurt a number of trivial benchmarks a few percent, but in the real world is more likely to be an improvement rather than a problem.
-- Greg Smith, 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance" Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
