Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table table_B (
 id int primary key, 
 deleted boolean
);
create table table_A (
 id serial primary key, 
 parent_id int,
 is_active boolean default true, 
 foreign_id int, 
 foreign key (foreign_id) references table_B(id)
);
insert into table_B (id, deleted) values
  (1, false), (2, true), (5, true)
, (3, false), (4, false)
5 rows affected

insert into table_A (parent_id, foreign_id) values
  (null, 1), (1, 2), (1, 5)
, (null, 3), (3, 4)
5 rows affected

EXPLAIN
UPDATE table_A A SET is_active = false 
FROM table_A 
WHERE A.parent_id IS NULL 
  AND A.is_active = true 
  AND A.id = ANY (
    SELECT (B.parent_id) 
    FROM table_A B 
    INNER JOIN table_B ON table_A.foreign_id = table_B.id 
    WHERE table_B.deleted = true
);


QUERY PLAN


Update on table_a a  (cost=0.00..314144.00 rows=4975 width=25)

  ->  Nested Loop  (cost=0.00..314144.00 rows=4975 width=25)

        Join Filter: (SubPlan 1)

        ->  Seq Scan on table_a  (cost=0.00..29.90 rows=1990 width=10)

        ->  Materialize  (cost=0.00..29.93 rows=5 width=18)

              ->  Seq Scan on table_a a  (cost=0.00..29.90 rows=5 width=18)

                    Filter: ((parent_id IS NULL) AND is_active)

        SubPlan 1

          ->  Nested Loop  (cost=0.15..57.97 rows=1990 width=4)

                ->  Index Scan using table_b_pkey on table_b  (cost=0.15..8.17 rows=1 width=0)

                      Index Cond: (table_a.foreign_id = id)

                      Filter: deleted

                ->  Seq Scan on table_a b  (cost=0.00..29.90 rows=1990 width=4)


EXPLAIN 
UPDATE table_A SET is_active = false 
WHERE parent_id IS NULL 
  AND is_active = true 
  AND id = ANY (
    SELECT a2.parent_id
    FROM table_A a2 
    JOIN table_B b ON a2.foreign_id = b.id 
    WHERE b.deleted = true
  );


QUERY PLAN


Update on table_a  (cost=92.26..122.20 rows=2 width=31)

  ->  Hash Join  (cost=92.26..122.20 rows=2 width=31)

        Hash Cond: (table_a.id = a2.parent_id)

        ->  Seq Scan on table_a  (cost=0.00..29.90 rows=5 width=18)

              Filter: ((parent_id IS NULL) AND is_active)

        ->  Hash  (cost=89.76..89.76 rows=200 width=16)

              ->  HashAggregate  (cost=87.76..89.76 rows=200 width=16)

                    Group Key: a2.parent_id

                    ->  Hash Join  (cost=50.14..85.27 rows=995 width=16)

                          Hash Cond: (a2.foreign_id = b.id)

                          ->  Seq Scan on table_a a2  (cost=0.00..29.90 rows=1990 width=14)

                          ->  Hash  (cost=34.70..34.70 rows=1235 width=10)

                                ->  Seq Scan on table_b b  (cost=0.00..34.70 rows=1235 width=10)

                                      Filter: deleted


EXPLAIN
UPDATE table_A AS parent
   SET is_active = false 
FROM (
   SELECT child.parent_id
   FROM table_A AS child 
   JOIN table_B AS dream
     ON child.foreign_id = dream.id
   WHERE child.parent_id IS NOT NULL
     AND dream.deleted = true
   GROUP BY child.parent_id
) dreamless 
WHERE parent.id = dreamless.parent_id
  AND parent.parent_id IS NULL
  AND parent.is_active = true;


QUERY PLAN


Update on table_a parent  (cost=117.68..122.21 rows=1 width=47)

  ->  Hash Join  (cost=117.68..122.21 rows=1 width=47)

        Hash Cond: (dreamless.parent_id = parent.id)

        ->  Subquery Scan on dreamless  (cost=87.72..91.72 rows=200 width=32)

              ->  HashAggregate  (cost=87.72..89.72 rows=200 width=4)

                    Group Key: child.parent_id

                    ->  Hash Join  (cost=50.14..85.25 rows=990 width=4)

                          Hash Cond: (child.foreign_id = dream.id)

                          ->  Seq Scan on table_a child  (cost=0.00..29.90 rows=1980 width=8)

                                Filter: (parent_id IS NOT NULL)

                          ->  Hash  (cost=34.70..34.70 rows=1235 width=4)

                                ->  Seq Scan on table_b dream  (cost=0.00..34.70 rows=1235 width=4)

                                      Filter: deleted

        ->  Hash  (cost=29.90..29.90 rows=5 width=18)

              ->  Seq Scan on table_a parent  (cost=0.00..29.90 rows=5 width=18)

                    Filter: ((parent_id IS NULL) AND is_active)


UPDATE table_A AS parent
   SET is_active = false 
FROM (
   SELECT child.parent_id
   FROM table_A AS child 
   JOIN table_B AS dream
     ON child.foreign_id = dream.id
   WHERE child.parent_id IS NOT NULL
     AND dream.deleted = true
   GROUP BY child.parent_id
) dreamless 
WHERE parent.id = dreamless.parent_id
  AND parent.parent_id IS NULL
  AND parent.is_active = true;
1 rows affected

SELECT * FROM table_A


id
parent_id
is_active
foreign_id


2
1
t
2

3
1
t
5

4
null
t
3

5
3
t
4

1
null
f
1