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. For more information, see the article Default_statistics_target for 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. For more information, see the article Parallelism VS temporary tables.

  • 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. For more information, see the article Speeding up the length of the queue of invalidation messages.

  • 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. For more information, see the article Optimizing the hash table of statistics.

  • The last restriction on parallel execution of queries with temporary tables has been lifted. The parallelism is now applied directly to the plan nodes reading from the temporary tables. Due to this, on real requests, the cost calculation procedures in 1C are accelerated from 2.3 to 3.4 times without any changes to the configuration code.

  • Full support for temporary tables on read-only replicas has been implemented using the enable_temp_memory_catalog and enable_temp_table_on_replica parameters. This makes the 1C database copy mechanism practically applicable on Tantor SE for the first time: load tests for several thousand users have confirmed successful horizontal scaling with the transfer of some queries to the replica without loss of performance. For more information, see the article 1C horizontal scaling: we transfer reports to a replica without loss of performance.

  • Native data compression is implemented directly during recording. This allows you to reduce the size of 1C databases by 5-10 times. Unlike solutions that require background workers and periodic defragmentation, compression occurs inline without accumulation of fragmentation and without constant additional overhead on the CPU and I/O in a productive environment.

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. For more information, see the article Speeding up query planning with high default_statistics_target values.

  • Accelerated the execution of queries with various scan types by optimizing the filter application order: the most selective conditions are checked first. For more information, see the article Speeding up query plan operators due to selectivity of condition fields.

  • 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. For more information, see the article Optimizing disjunctive subqueries.

  • The output of the EXPLAIN command has been significantly expanded. It now includes detailed cost metrics, statistics on plan nodes, and explanations of the planner's key decisions. This allows you to more quickly diagnose the reasons for choosing suboptimal execution plans.

  • A new cpu_filter_cost parameter has been implemented. It allows you to more precisely configure the cost model of the planner and automatically avoid inefficient Nested Loop plans that generate redundant "Rows Removed by Filter" operations. This is a critical optimization for typical 1C queries with multiple table connections and high filter selectivity.

  • The cardinality_estimation parameter has been added. It allows you to switch to the “Partially correlated” cardinality estimation model, which takes into account the correlation between fields and significantly more accurately estimates the number of rows in complex multi-table queries. This architecture is typical for 1C databases, where dependent fields and composite connections have traditionally misled the planner, leading to suboptimal execution plans.

  • The problem of quadratic complexity of O(N×M) has been eliminated when planning queries with the WHERE field IN construction [...]. Now, instead of iterating through all the values of the MCV list against each element of the input array, a hash join with a total complexity of O(N+M) is used. This reduces the planning time for such queries in 1C by up to 100 times, which is especially noticeable on large databases with high default_statistics_target values.

  • The planner is optimized based on pre-aggregation of data before joining tables. Now, instead of the classic Join→Aggregate scheme, the planner is able to automatically apply the Aggregate→Join scheme, which reduces the intermediate set of rows by orders of magnitude. Based on real 1C analytical queries (closing of the month, cost calculation) this allows execution to be accelerated up to 50 times without any changes to the configuration code.

  • The Index Skip Scan technology is implemented. It solves the typical 1C problem of fully scanning a composite index when an intermediate field is missing in the query condition. Now the Executor iteratively determines the possible values of the missing field and reuses the index for a point search, which speeds up by orders of magnitude on real queries to accumulation registers. This is especially critical for multi-user databases with shared data.

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.

  • The auto_dump extension has been added, which automatically saves the full context of a problematic query - including DDL and temporary table data - into a set of ready-made SQL files. This allows you to reproduce any query from 1C in psql with three commands without any actions in the platform interface and significantly speeds up the diagnosis of suboptimal DBMS plans and errors in a productive environment.

  • The pg_sample_profile extension has been implemented, which allows you to profile Tantor SE wait events directly on a running high-load system without restarting the server.

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