Major changes in this release:
* IN/NOT IN subqueries are now much more efficient
In previous releases, IN/NOT IN subqueries were joined to the
upper query by sequentially scanning the subquery looking for a
join. The 7.4 code uses the same sophisticated techniques used by
ordinary joins and so is much faster, and is now faster than
EXISTS subqueries.
* Improved GROUP BY processing by using hash buckets
In previous releases, GROUP BY totals were accumulated by
sequentially scanning the list of groups looking for a match; the
7.4 code places GROUP BY values in hash buckets so the proper
match can be found much quicker. This is particularly significant
in speeding up queries that have a large number of distinct GROUP
BY values.
* New multi-key hash join capability
In previous releases, hash joins could only occur on single-column
joins. This release allows multi-column hash joins.
* ANSI joins are now better optimized
Prior releases evaluated ANSI join syntax only in the order
specified by the query; 7.4 allows full optimization of queries
using ANSI join syntax, meaning the optimizer considers all
possible join orderings and chooses the most efficient.
* Faster and more powerful regular expression code
The entire regular expression module has been replaced with a new
version by Henry Spencer, originally written for TCL. The code
greatly improves performance and supports several flavors of
regular expressions.
* Function-inlining for simple SQL functions
Simple SQL functions can now be inlined by including their SQL in
the main query. This improves performance by preventing repeated
calls to the SQL function --- this allows simple SQL functions to
behave like macros.
* Full support for IPv6 connections and IPv6 address data types
Prior releases allowed only IPv6 connections and IP data types
only supported IPv4 addresses. This release adds full IPv6 support
in both of these areas.
* Major improvements in SSL performance and reliability
Several people very familiar with the SSL API have overhauled our
SSL code to improve SSL key negotiation and error recovery.
* Allow free space map to efficiently reuse empty index pages, and
other free space management improvements.
In prior releases, index pages that were left empty because of
deleted rows could only be reused by rows with index values
similar to the original rows indexed on that page. In 7.4, VACUUM
records empty index pages and allows them to be used for any
future index rows.
* Implement information schema
* Support for read-only transactions
* Make cursors comply more closely with the SQL standard
* New protocol improves connection speed/reliability, and adds error
codes, status information, a binary protocol, error reporting
verbosity, and cleaner startup packets.
* Allow cursors to exist outside transactions, also called holdable
cursors
* libpq and ecpg are now fully thread-safe with --enable-thread-safety
While prior libpq releases already supported threads, this release
improves thread safety by fixing some non-thread-safe code that
was used in the database connection routines.
* New version of full text indexing in /contrib/tsearch2
* New autovacuum tool in /contrib
This new tool monitors the database statistics tables for
INSERT/UPDATE/DELETE activity and automatically vacuums tables
when needed.
* Array handling has been improved and moved into the main server
Many array limitations have been removed and they behave more like
fully-supported data types.
fixed.