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,4e6::int,1::int) number; 

DROP TABLE

DROP TABLE

DROP TABLE

SELECT 4000001

select (random()*1e6)::int random_number
into random_numbers1
from generate_series(0,1e5,1) numbers;
select (random()*1e6)::int random_number
into random_numbers2
from generate_series(0,1e5,1) numbers;
--select count(*) from random_numbers1 where random_number in (select random_number from random_numbers2);
--select length(random_number::text),count(*) from random_numbers1 group by 1 order by 2 desc;

SELECT 100001

SELECT 100001

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..89112.62 rows=0 width=0) (actual time=6925.592..6925.594 rows=0 loops=1)

  ->  Seq Scan on numbers  (cost=3710.12..89112.62 rows=1128375 width=6) (actual time=71.343..2699.003 rows=3818613 loops=1)

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

        Rows Removed by Filter: 181388

        SubPlan 1

          ->  Seq Scan on random_numbers1  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.017..10.792 rows=100001 loops=1)

        SubPlan 2

          ->  Seq Scan on random_numbers2  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.022..11.073 rows=100001 loops=1)

Planning Time: 0.208 ms

Execution Time: 6926.256 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..97385.94 rows=0 width=0) (actual time=6464.916..6464.919 rows=0 loops=1)

  ->  Seq Scan on numbers  (cost=23267.19..97385.94 rows=2256750 width=6) (actual time=197.911..2140.999 rows=3818613 loops=1)

        Filter: (NOT (hashed SubPlan 1))

        Rows Removed by Filter: 181388

        SubPlan 1

          ->  HashAggregate  (cost=18677.99..22702.37 rows=225930 width=4) (actual time=100.588..155.515 rows=181388 loops=1)

                Group Key: random_numbers1.random_number

                Planned Partitions: 4  Batches: 5  Memory Usage: 10801kB  Disk Usage: 3400kB

                ->  Append  (cost=0.00..6534.25 rows=225930 width=4) (actual time=0.027..36.551 rows=200002 loops=1)

                      ->  Seq Scan on random_numbers1  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.026..9.712 rows=100001 loops=1)

                      ->  Seq Scan on random_numbers2  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.021..10.178 rows=100001 loops=1)

Planning Time: 0.161 ms

Execution Time: 6466.679 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..78958.52 rows=0 width=0) (actual time=8451.911..8451.913 rows=0 loops=1)

  ->  Seq Scan on numbers  (cost=4839.78..78958.52 rows=2256750 width=6) (actual time=106.271..1983.396 rows=3818613 loops=1)

        Filter: (NOT (hashed SubPlan 1))

        Rows Removed by Filter: 181388

        SubPlan 1

          ->  Append  (cost=0.00..4274.95 rows=225930 width=4) (actual time=0.026..43.466 rows=200002 loops=1)

                ->  Seq Scan on random_numbers1  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.025..12.109 rows=100001 loops=1)

                ->  Seq Scan on random_numbers2  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.019..11.406 rows=100001 loops=1)

Planning Time: 0.134 ms

Execution Time: 8452.682 ms

EXPLAIN

ROLLBACK