Postgres 15

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?.
drop table if exists numbers;
drop table if exists random_numbers1;
drop table if exists random_numbers2;

select number
into numbers
from generate_series(0::int,10e5::int,1::int) number;

DROP TABLE

DROP TABLE

DROP TABLE

SELECT 1000001

/*by design, there's no overlap between these tables, making deduplication unnecessary*/
select (random()*10e5)::int random_number
into random_numbers1
from generate_series(1,1e5,1) numbers;

select (random_number+10e5)::int random_number
into random_numbers2
from random_numbers1;

SELECT 100000

SELECT 100000

begin transaction;

explain analyze
delete from numbers
where number not in (select random_number from random_numbers1)
and 
number not in (select random_number from random_numbers2);

rollback;

BEGIN

QUERY PLAN

Delete on numbers  (cost=3710.12..25060.75 rows=0 width=0) (actual time=8165.969..8165.971 rows=0 loops=1)

  ->  Seq Scan on numbers  (cost=3710.12..25060.75 rows=282094 width=6) (actual time=455.452..4032.441 rows=904902 loops=1)

        Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))

        Rows Removed by Filter: 95099

        SubPlan 1

          ->  Seq Scan on random_numbers1  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.023..57.760 rows=100000 loops=1)

        SubPlan 2

          ->  Seq Scan on random_numbers2  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.027..115.486 rows=100000 loops=1)

Planning Time: 0.247 ms

Execution Time: 8166.604 ms

EXPLAIN

ROLLBACK

begin transaction;

explain analyze
delete from numbers
where number not in (
  select random_number from random_numbers1
  union
  select random_number from random_numbers2);

rollback;

BEGIN

QUERY PLAN

Delete on numbers  (cost=23267.19..41796.88 rows=0 width=0) (actual time=7142.169..7142.171 rows=0 loops=1)

  ->  Seq Scan on numbers  (cost=23267.19..41796.88 rows=564188 width=6) (actual time=1309.705..3334.192 rows=904902 loops=1)

        Filter: (NOT (hashed SubPlan 1))

        Rows Removed by Filter: 95099

        SubPlan 1

          ->  HashAggregate  (cost=18677.99..22702.37 rows=225930 width=4) (actual time=555.647..1121.804 rows=190198 loops=1)

                Group Key: random_numbers1.random_number

                Planned Partitions: 4  Batches: 5  Memory Usage: 11057kB  Disk Usage: 3440kB

                ->  Append  (cost=0.00..6534.25 rows=225930 width=4) (actual time=0.031..173.960 rows=200000 loops=1)

                      ->  Seq Scan on random_numbers1  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.029..55.446 rows=100000 loops=1)

                      ->  Seq Scan on random_numbers2  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.024..11.839 rows=100000 loops=1)

Planning Time: 0.161 ms

Execution Time: 7143.877 ms

EXPLAIN

ROLLBACK

begin transaction;

explain analyze
delete from numbers
where number not in (
  select random_number from random_numbers1
  union all
  select random_number from random_numbers2);

rollback;

BEGIN

QUERY PLAN

Delete on numbers  (cost=4839.78..23369.46 rows=0 width=0) (actual time=6482.811..6482.814 rows=0 loops=1)

  ->  Seq Scan on numbers  (cost=4839.78..23369.46 rows=564188 width=6) (actual time=512.727..2516.804 rows=904902 loops=1)

        Filter: (NOT (hashed SubPlan 1))

        Rows Removed by Filter: 95099

        SubPlan 1

          ->  Append  (cost=0.00..4274.95 rows=225930 width=4) (actual time=0.025..178.749 rows=200000 loops=1)

                ->  Seq Scan on random_numbers1  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.023..101.011 rows=100000 loops=1)

                ->  Seq Scan on random_numbers2  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.018..12.398 rows=100000 loops=1)

Planning Time: 0.111 ms

Execution Time: 6483.513 ms

EXPLAIN

ROLLBACK