4. 1C improvements#

4. 1C improvements
4. 1C improvements
Prev UpPrefaceHome Next

4. 1C improvements #

4.1. Core
4.2. Planner
4.3. Modules

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. For more information, see the article Improving the accuracy of statistics without increasing default_statistics_target.

  • Implemented delayed placement of temporary tables: disk space is reserved only when data is actually written, significantly reducing I/O pressure. For more information, see the article Reducing the load on the disk subsystem.

  • 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. For more information, see the article Optimizing the way temporary tables store metadata.

  • Optimized operation of LIKE operator for mchar/mvarchar types by using more efficient Unicode functions for simple substring patterns. For more information, see the article Speeding up queries with substring search.

  • Optimized operation of ANALYZE command for wide tables. This provides an order of magnitude increase in the performance of the ANALYZE command for wide tables. For more information, see the article Optimization of statistics calculation.

  • Introduced a new parameter default_statistics_target_temp_tables to control the accuracy of temporary tables statistics, which, in combination with default_statistics_target, provides independent configuration of the level of detail in statistics for permanent and temporary tables.

  • Expanded parallel query execution support: parallelism is now available when writing results into temporary tables, as well as in queries reading data from temporary tables in the SELECT section.

  • Increased the invalidation message queue length from 16384 to 32768, which reduces the likelihood of buffer overflow and the need for complete cache invalidation under high loads with intensive creation of temporary tables.

  • Implemented the ability to disable the collection of statistics on temporary tables in the general Tantor Postgres hash table. This solves the problem of LWLock locks and performance degradation with frequent operations with temporary tables, statistics on which are not critical for the operation of the system.

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 and AND operations in WHERE 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 into LATERAL SEMI JOIN, significantly improving performance of complex analytical queries involving row-level security (RLS) policies in 1C. For more information, see the article Speeding up record-level access restriction queries (RLS).

  • 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. For more information, see the article Speeding up queries to virtual tables using Join Predicate Pushdown.

  • Additionally optimized execution of queries using aggregate functions by reordering grouping columns to better align with presorted input. For more information, see the article Speeding up analytical queries with data aggregation.

  • Improved the algorithm for calculating selectivity for fields used in JOIN conditions, so that increasing the default_statistics_target parameter no longer increases overhead costs at the query planning stage.

  • Accelerated the execution of queries with various scan types by optimizing the filter application order: the most selective conditions are checked first.

  • Added optimization for queries with subquery disjunction (constructions like WHERE EXISTS(...) OR EXISTS(...)): the planner now automatically converts them into a union of independent queries via UNION ALL with the elimination of duplicates, which makes it possible to apply index access for each condition separately instead of a full scan of the table.

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. For more information, see the article Automatic creation of statistics.

  • 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. For more information, see the article Normalization of temporary table names.

Prev Up Next
3. What are the differences between Tantor SE 1C 16 and PostgreSQL 16 Home 5. Conventions