Postgresql – (How) does table inheritance interfere with locking in postgres

lockingpartitioningpostgresql

I have a table inheritance setup that might be simplified like this:

CREATE TABLE p (
    id BIGSERIAL PRIMARY KEY,
    type_id BIGINT,
    approved BOOLEAN
);

CREATE INDEX ON p(approved);

CREATE TABLE a (
    a_field VARCHAR,
    PRIMARY KEY (id),
    CHECK(type_id = 1::BIGINT)
) INHERITS (p);

CREATE TABLE b (
    b_field INT,
    PRIMARY KEY (id),
    CHECK(type_id = 2::BIGINT)
) INHERITS (p);

CREATE INDEX ON b(approved);

CREATE TABLE c (
    c_field NUMERIC,
    PRIMARY KEY (id)
    -- this table is missing the check constraint (for no good reason)
) INHERITS (p);

I now have a long-ish running transaction that locks table a. While this is running, I observe queries on table b that wait for that lock to be released, even though they are guaranteed to not use the data in a. For example:

SELECT TRUE FROM p WHERE id = 12345 AND approved = false AND type_id = 2 LIMIT 1;

Running EXPLAIN on that query shows something like this:

                              QUERY PLAN                                                                  
----------------------------------------------------------------------------
Limit  (cost= ...)
    ->  Result  (cost= ...)
       ->  Append  (cost= ...)
         ->  Seq Scan on p  (cost= ...)
                 Filter: ((NOT approved) AND (id = 12345) AND (type_id = 2))
         ->  Index Scan using b_approved on b p  (cost= ...)
                 Index Cond: (approved = false)
                 Filter: ((NOT approved) AND (id = 12345) AND (type_id = 2))

I am not sure if I'm reading that correctly, but it appears to me that the query planner thinks it needs to go through the parent table because it does not know whether table c might contain the rows it's looking for. In reality, there's more than one table (in fact, a couple of dozens) like c, which lack the CHECK constraint, so it seems plausible to me that the planner thinks a Seq Scan on the parent might be the most efficient route.

Will I avoid locks on a interfering with an unrelated (in terms of data) query on b if I add proper check constraints to c (the c-like tables all could get one, it would be some work, though)?

Are there other remarks on this kind of setup with regard to locking?

Note that one of the reasons why this partitioning exists is that the application running the queries has parts that are agnostic to what child table it is dealing with, hence the query shown goes to p. Additionally, such a query might affect a small number of tables (3-5) instead of just b, i. e., the query plan shows additional Index Scan lines like the one for b shown here.

Best Answer

Apparently, you want to see constraint exclusion at doing its work. As in similar setups the parent table has no constraint (and, just to mention it, usually no rows), it will be always visited by queries on the parent table. Using your example schema, see the output:

test=# EXPLAIN SELECT count(1) FROM p;
                            QUERY PLAN                             
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=576.00..576.01 rows=1 width=0)
   ->  Append  (cost=0.00..501.00 rows=30001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
         ->  Seq Scan on a  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on b  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on c  (cost=0.00..173.00 rows=10000 width=0)

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN                             
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=412.00..412.01 rows=1 width=0)
   ->  Append  (cost=0.00..387.00 rows=10002 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on c  (cost=0.00..198.00 rows=1 width=0)
               Filter: (type_id = 1)

In the second case, table b is not visited, because the planner knows for sure that there cannot be any rows matching the condition. At the same time, p is present in both cases. The same applies to c, too: it has no easy way (the check) to exclude the table from the plan. In this case, it has to visit all tables that have the possibility to contain the actual rows.

Let's add some check there, too:

ALTER TABLE c ADD CHECK (type_id = 3);

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN                             
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=214.00..214.01 rows=1 width=0)
   ->  Append  (cost=0.00..189.00 rows=10001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)

Much better now.

Now let's have a look on the locks that are taken.

BEGIN; -- to be able to see the locks

SELECT count(1) FROM p WHERE type_id = 1;

SELECT relname, locktype, mode
  FROM pg_locks
  JOIN pg_class c ON relation = c.oid 
  JOIN pg_namespace n ON c.relnamespace = n.oid
 WHERE nspname = 'dba' 
   AND relkind = 'r';

 relname │ locktype │      mode       
─────────┼──────────┼─────────────────
 c       │ relation │ AccessShareLock
 b       │ relation │ AccessShareLock
 a       │ relation │ AccessShareLock
 p       │ relation │ AccessShareLock

This is not so nice... Apparently (and somewhat surprisingly), going to the parent table locks all children, too.

But:

ROLLBACK;
BEGIN; -- to be able to see the locks

SELECT count(1) FROM a WHERE type_id = 1; -- touching only "a"

-- the locks taken:
 a       │ relation │ AccessShareLock

This means that if you can constraint your query to touch only a subset of the tables, it will save you the locks on the other children. Playing around a bit it shows that this applies to other statements (for example, an UPDATE), too, just the lock modes are different.

Related Question