[HACKERS] Summary of Changes since last release (7.4.1)
POSTGRESQL: Summary of Changes since last release (7.4.1) -- 10 Feb 2004 This is a summary of most changes since code versions marked 7_4_1. The intention is to help everybody understand what's coming and what might be affected, though most importantly, where YOU might add value to the community as a designer, developer, tester, technical author or advocate. It seeks to complement other information sources such as Robert Treat's weekly news bulletin, the TODO summary of desired future items, or the README files - which are the definitive list of what's in any particular release. Your feedback is appreciated. So far in this release dev cycle, major functionality will effect - PERFORMANCE - OPTIMIZER/EXECUTOR - ROBUSTNESS - SECURITY Other code changes are summarised and their major impacts noted. These notes cover major changes and are not guaranteed complete, or even fully tested. Many additional patches to the latest full release have been submitted and these are appreciated just as much, even though they have *mostly* more isolated effects. Documentation changes continue, though aren't described here, neither are client side utilities/interfaces. Nothing mentioned here is DEFINITELY in 7.5 or any future release; testing of everything mentioned here is encouraged and appreciated, for regression, performance and robustness. There is not yet a CVS branch specifically for any later release than 7_4_1; these changes are not yet even guaranteed to build into a consistent release when taken together. Description of changes is designed to highlight benefit and impact, as well as identifying specific areas of code change and potential knock-on effects. MAJOR FUNCTIONALITY 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. (Tom) Implications for user defined types and indices also? [HACKERS] 8-Nov-03 - All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) - 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 so that we 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) OPTIMIZER/EXECUTOR IMPROVEMENTS - Genetic Optimizer usage has been re-analyzed; geqo defaults have now been set to more effective values which are expected to significantly improve plan selection for complex multi-way joins (> 10-way). geqo_effort setting now offers an easy 1..10 setting (like IBM DB2), that allows this
[HACKERS] Summary of Changes since last release (7.4.1)
POSTGRESQL: Summary of Changes since last release (7.4.1) -- 18 Feb 2004 This is a summary of most changes since code versions marked 7_4_1. The intention is to help everybody understand what's coming and what might be affected, though most importantly, where YOU might add value to the community as a designer, developer, tester, technical author or advocate. It seeks to complement other information sources such as Robert Treat's weekly news bulletin, the TODO summary of desired future items, Elein Mustain's General Bits or the README files - which are the definitive list of what's in any particular release. *** Your feedback is appreciated. *** So far in this release dev cycle, major functionality will effect - PERFORMANCE - OPTIMIZER/EXECUTOR - ROBUSTNESS - SECURITY - WIN32 PORTABILITY Other code changes are summarised and their major impacts noted. These notes cover major changes and are not guaranteed complete, or even fully tested. Many additional patches to the latest full release have been submitted and these are appreciated just as much, even though they have *mostly* more isolated effects. Documentation changes continue, though aren't described here, neither are client side utilities/interfaces. Nothing mentioned here is DEFINITELY in 7.5 or any future release; testing of everything mentioned here is encouraged and appreciated, for regression, performance and robustness. There is not yet a CVS branch specifically for any later release than 7_4_1; these changes are not yet even guaranteed to build into a consistent release when taken together. Description of changes is designed to highlight benefit and impact, as well as identifying specific areas of code change and potential knock-on effects. MAJOR FUNCTIONALITY 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 th
Re: [HACKERS] Summary of Changes since last release (7.4.1)
Simon Riggs wrote: - All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) That is great news! Looking forward to 7.5 already, Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Summary of Changes since last release (7.4.1)
- All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) That is great news! Looking forward to 7.5 already, I could have sworn that the above was done in 7.4, by Tom...? Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Summary of Changes since last release (7.4.1)
Christopher Kings-Lynne wrote: - All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) That is great news! Looking forward to 7.5 already, I could have sworn that the above was done in 7.4, by Tom...? Yeah. 7.4 performed a lot better for me. I was a little too greedy in my hopes that the temp table issue hadn't been addressed and would see further temp table-related performance gains in 7.5. I see the CVS commit Aug. 6 before the pgindent run Sept. 4: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/smgr/smgr.c?rev=1.58&content-type=text/x-cvsweb-markup Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Summary of Changes since last release (7.4.1)
I wrote: Yeah. 7.4 performed a lot better for me. I was a little too greedy in my hopes that the temp table issue hadn't been addressed and would see further temp table-related performance gains in 7.5. I see the CVS commit Aug. 6 before the pgindent run Sept. 4: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/smgr/smgr.c?rev=1.58&content-type=text/x-cvsweb-markup Actually, that was an Aug 6, 2002 commit, not 2003 which would make it 7.3, right? So Simon, my I humbly ask from where you culled this change in CVS tip? Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Summary of Changes since last release (7.4.1)
>Mike Mascari > Actually, that was an Aug 6, 2002 commit, not 2003 which would make > it 7.3, right? So Simon, my I humbly ask from where you culled this > change in CVS tip? Wow, looks like you got me there. It is I who humbly apologises. Humble AND Zealous though :) Overall, each item has been researched a bit more than simply copying the committers' notes, but as you can see, I still have much to learn about both the codebase itself and the way it is developed. The notes are an attempt to help others do the same - accuracy is important - I'll do better next time. Thanks for pointing out my error, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Summary of Changes since last release (7.4.1)
Simon, Excellent job, your summaries have saved me hours of reading the hackers mailing list. Keep up the good work! On Wed, 2004-02-11 at 00:21, Simon Riggs wrote: > >Mike Mascari > > Actually, that was an Aug 6, 2002 commit, not 2003 which would make > > it 7.3, right? So Simon, my I humbly ask from where you culled this > > change in CVS tip? > > Wow, looks like you got me there. It is I who humbly apologises. > > Humble AND Zealous though :) > > Overall, each item has been researched a bit more than simply copying > the committers' notes, but as you can see, I still have much to learn > about both the codebase itself and the way it is developed. The notes > are an attempt to help others do the same - accuracy is important - I'll > do better next time. > > Thanks for pointing out my error, Simon Riggs > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Mike Benoit <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Summary of Changes since last release (7.4.1)
Simon, On Thu, 19 Feb 2004 00:05:15 -, "Simon Riggs" <[EMAIL PROTECTED]> wrote: >POSTGRESQL: Summary of Changes since last release (7.4.1) >-- >18 Feb 2004 this is getting long over time. If you plan to post it once a week, flagging items that are new or have changed might help keeping track of what's going on. >- Index performance improved when scanning highly non-unique indices; ! Index performance improved when scanning highly non-unique indices; or - (updated) Index performance improved ... >- ANALYZE will now collect statistics on expressional indexes, and make + ANALYZE will now collect statistics on expressional ... or - (new) ANALYZE will now collect statistics on expressional ... Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html