Re: Performance improvements/regressions from 8.4 to 9.0?

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@2ndQuadrant.com 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