4. 1C improvements#
4. 1C improvements
4.1. Core
The problem of light locks with a large number of simultaneous DBMS connections has been solved.
The maximum size of data in table cells supported by the pg_dump module has been increased from 1 GB to 2 GB.
The values of the parameters have been changed, which, with default values, could lead to a deterioration in 1C performance.
Operation of locks improvements for temporary tables:
System catalog entries for temporary tables no longer acquire catalog-level locks.
Optimized invalidation messaging for temporary tables by replacing
spin lock
with memory barrier.
The opportunity to improve the accuracy of scalar statistics estimation for tables with complex data distribution distributions without increasing
default_statistics_target
parameter has been implemented.Implemented delayed placement of temporary tables: disk space is reserved only when data is actually written, significantly reducing I/O pressure.
Optimized handling mechanism of temporary tables by storing information in RAM memory instead of system catalogs. This avoids “system catalog bloat”, particularly in high-load or resource-constrained environments. Also reduced the number of disk synchronization system calls during temporary table operations.
Optimized operation of
LIKE
operator formchar/mvarchar
types by using more efficient Unicode functions for simple substring patterns.Optimized operation of
ANALYZE
command for wide tables. This provides an order of magnitude increase in the performance of theANALYZE
command for wide tables.
4.2. Planner
Optimized execution of queries using aggregate functions by changing the order of the grouping fields.
Improved prediction of table joins with multi-column indexes, which allows you to get a more accurate estimate of the number of rows and select the optimal query plan operator.
Caching of temporary tables has been optimized by reducing the list of invalidations.
Various operations with the list of columns used in the query have been optimized to reduce the overhead of executing queries and optimize memory usage.
Query execution has been optimized by highlighting common conditions for
OR
andAND
operations inWHERE
expressions, in order to speed up filtering at the early stages of their processing.Execution of queries using the
IN (&Array Of Values)
construct has been accelerated. For more information, see the article Solving the Values problem in Postgres.Fixed an error of incorrect evaluation of rows as a result of joining several tables, when 0 rows were selected from one table and additional conditions were imposed on the result of the connection.
Improved the algorithm for evaluating selectivity based on histograms for queries using joins across multiple columns of tables.
The algorithm for selecting a suitable index has been improved by taking into account the selectivity of each index considered by the scheduler. This makes it more likely to use an index that covers all conditions, which leads to faster query execution and savings in computing resources. As an example, this allows you to speed up the update of the totals of the accumulation registers. For more information, see the article Tantor Special Edition 1C DBMS: speeding up the update of accumulation register totals.
Fixed an error in the
multicolumn index
mechanism (locate_inner_multicolumn_index
function), which leads to the selection of an incorrect index to determine selectivity under specified conditions.Implemented transformation of
EXISTS
subqueries intoLATERAL SEMI JOIN
, significantly improving performance of complex analytical queries involving row-level security (RLS) policies in 1C.Implemented mechanism
Join Predicate Pushdown
, which significantly improves the performance of complex analytical queries, such as those involving virtual tables of information registers, accumulation, and accounting.Additionally optimized execution of queries using aggregate functions by reordering grouping columns to better align with presorted input.
4.3. Modules
Logical replication slots are supported for the database copy mechanism in dbcopies_decoding.
The
SELECT FASTTRUNCATE
function, designed to truncate temporary tables, has been accelerated in fasttrun. For more information, see the article Temporary tables and SELECT FASTTRUNCATE.The execution of queries using temporary tables that were created from the table of values passed to the query as a parameter has been accelerated in online_analyze. For more information, see the article 1C and the Tantor DBMS: the history of one implementation.
The
pg_trace
extension has been implemented, designed for convenient profiling of SQL queries according to the established conditions for collecting traces. For more information, see the pg_trace documentation and the article pg_trace — query tracer from Tantor Labs.Introduced
pg_stat_advisor
extension, which automatically analyzes query plans to detect inaccurate planner estimates. The extension provides specific recommendations for extended statistics creation or creates them automatically, depending on configuration. In some cases, this can greatly improve performance of critical queries without requiring manual tuning or deep query analysis.Adapted pg_stat_statements extension for 1C-specific workloads: the extension now automatically normalizes temporary table names, enabling more meaningful aggregation of statistics for structurally identical queries that differ only by temporary table names.