Tantor Labs analyzed performance issues with dynamic lists in 1C software following migrations from MS SQL to PostgreSQL. Users reported significant delays, with some operations taking up to 8 seconds to complete due to inefficient query execution plans.
The investigation revealed that the PostgreSQL query planner struggled with complex queries involving many tables. Specifically, the join_collapse_limit and from_collapse_limit parameters, set to 20, prevented the planner from optimizing the join order for tables listed beyond that threshold.
Increasing these parameters proved ineffective, as tests on 1C:ERP showed performance degradation and increased RAM consumption. A query with 77 tables took 198 seconds to execute, confirming that adjusting these limits is not a viable long-term solution.
Tantor Postgres developers implemented a fix by modifying the planner to prioritize SEMI JOIN nodes during query planning. This ensures that filters significantly reducing row counts are processed earlier, regardless of their position in the query structure.
Following the update, the execution time for the problematic document journal query dropped from 8 seconds to 0.1 seconds. The team also addressed similar performance bottlenecks caused by sorting operations in dynamic lists.

