PERFORMANCE
- Major new memory buffer cache algorithm has now been implemented using
the Adaptive Replacement Cache algorithm. The implementation should have
positive benefit for everybody's workload, since ARC will adapt to a
variety of situations and has been designed to allow Vacuum to avoid
interfering with user applications. (Jan) src/backend/buffer
- New performance profiling of Intel CPU has allowed new spinlock code
to achieve performance/throughput gains of up to 10% using DBT-2 (OLTP)
workloads. Further gains to follow? (Manfred Spraul/Tom)
src/backend/storage/lmgr/s_lock.c
- TIP 9 now needs changing! Cross-data-type comparisons are now
indexable by btrees. All the existing cross-type comparison operators
(int2/int4/int8) and (float4/float8) have appropriate support. Also
(date/timestamp) comparisons allow use of indexes for expressions like
datecol >= date 'today' - interval '1 month' (Tom) Implications for user
defined types and indices also? [HACKERS] 8-Nov-03
- Index performance improved when scanning highly non-unique indices;
will greatly improve performance of cursor/fetch logic. B-tree's
initial-positioning-strategy code has been improved for the case when
index scans are caused by a WHERE indexcol > something. We now start
scan at first entry, rather than reading in all entries that share that
index value before we begin to scan. (Tom, after Dimitry Tkach)
- Heap access code is now faster when using compressed columns in-line;
previous assumption was that all compressed columns were also toasted
(Tom)
- Optimized calling performance for dynamically loaded C functions. Hash
table added to cache lookups of 'C'-language functions. Some limited
testing suggests that this puts the lookup speed for external functions
just about on par with built-in functions. (Tom)
- New delay feature added to VACUUM, allowing it to be executed in at a
lower priority, ensuring other concurrent transaction performance can be
maintained at a predictable level. Detailed analysis and graphs of
run-time behaviour available at
http://developer.postgresql.org/~wieck/vacuum_cost/ (Jan)
Extended to include VACUUM FULL,ANALYZE and non-btree index vacuums.
Centralize implementation of delay code by creating a pg_usleep()
subroutine in src/port/pgsleep.c. (Tom)
- More flexible memory control will allow large memory allocations to
large maintenance operations such as CREATE INDEX, without effecting
normal memory usage for queries. Rename server parameters SortMem and
VacuumMem to work_mem and maintenance_work_mem; old names still
available via new backward compatibility feature. Make btree index
creation and initial validation of foreign-key constraints use
maintenance_work_mem rather than work_mem as their memory limit. (Tom)
- Restructure smgr API as per detailed proposal of 6 Feb, to improve
performance in bgwriter and background checkpoint processes. Possibly
also a precursor to later implementation of Tablespaces... (Tom)
- ANALYZE will now collect statistics on expressional indexes, and make
use of them during optimization in majority of cases. (Tom)
- Repaired longstanding oversight in separate ANALYZE command: it
updated the pg_class.relpages and reltuples counts for the table proper,
but not for indexes. Greater planning accuracy should now result. (Tom)
