Unnecessary locks for partitioned tables - Mailing list pgsql-general

Hello!

Recently I`ve been pushing into life a new project and immediately experienced an Out of shared memory error while querying partitioned tables.

Imagine a huge busy table that you want to split into hundreds of partitions by list. Partition key is a kind of surrogate key that can be calculated outside of database and can be provided as parameter.

create table t (a int) partition by list (a);

select format('create table %I partition of t for values in (%s)', 'p'||x, x)

from generate_series(0,20) x

Query is executed inside stored procedure or as a prepared statement by, let’s say, Spring JPA.

prepare s (int) as select * from t where a = $1;

explain (analyze) execute s(0);

At the beginning database (12,13,14, or 15 versions) will try to create a custom execution plan like this one:

Query Text: prepare s (int) as select * from t where a = $1;

Seq Scan on p0 t  (cost=0.00..41.88 rows=13 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  Filter: (a = 0)

We have only one partition examined. But suddenly database decides to create more generic query plan to deal with all range of partitions.

begin;

set local plan_cache_mode = force_generic_plan;

Now all the queries looks like:

Query Text: prepare s (int) as select * from t where a = $1;

Append  (cost=0.00..880.74 rows=273 width=4) (actual time=0.014..0.015 rows=0 loops=1)

  Subplans Removed: 20

  ->  Seq Scan on p0 t_1  (cost=0.00..41.88 rows=13 width=4) (actual time=0.013..0.013 rows=0 loops=1)

        Filter: (a = $1)

Here the most important part is a count of removed plans. Database realizes that all partitions can be skipped except interested one. But under load we start receiving errors:

ERROR: out of shared memory

  Hint: You might need to increase max_locks_per_transaction.

Ok, let`s increase max_locks_per_transaction, but why this type of query produces so much locks? Looks like DB issues locks for all the partitioned objects involved in query and ONLY AFTER THAT it does partition pruning.

Here are locks :

select relation::regclass, mode

from   pg_locks

where  pid = pg_backend_pid() and

       locktype = 'relation' and relation::regclass <> 'pg_locks'::regclass;

"p14"     "AccessShareLock"

"p20"     "AccessShareLock"

"p17"     "AccessShareLock"

"pg_type_oid_index"       "AccessShareLock"

"p19"     "AccessShareLock"

....... etc

But for real life there are also indexes, keys, up to 5000 locks in my case for single select.

Setting plan_cache_mode = force_custom_plan resolves issue and also makes DB more performant (*5 in my case, literally from 140 to 650 executions per second on my development pc).

I`m calling to PG authors, is there any way to optimize prepared queries and minimize unnecessary locks in generic query plan? For instance, lock parent table, eliminate unnecessary partitions, lock remining objects. This may help much in any place where partition pruning possible.

Meanwhile I`m using force_custom_plan - no more huge peaks, all queries are at the lowest seen execution time. As a drawback there must be an overhead to compile plans every time.

Thanks,

Nikolay