On Wed, 7 Sept 2022 at 07:40, Levi Aul <levi@covalenthq.com> wrote:
> In other words, our workload is inherently one that acquires "way too many locks." Our largest performance
bottleneck,according to pg_wait_sampling, is the LockManager itself. Despite most of our queries spending only
millisecondsactually executing, they often spend seconds during planning waiting to acquire hundreds of access-shared
locks.
It would be good to have a better understanding of the problem you're
facing. There have been many changes to table partitioning since
PostgreSQL 10 and many of those changes affect the number of
partitions which are locked for certain classes of queries.
It would be good to know the following:
1. Which version of PostgreSQL are you having this problem with, and;
2. Example of queries you're having this problem with.
If you share that information we may be able to inform you about
features/performance improvements in newer versions which help with
the problem you're facing.
You mention "constraint-exclusion", that's no longer how we perform
partition pruning and hasn't been since (if I remember correctly)
PostgreSQL 11. Perhaps you're using PG10?
David