Postgres 12

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 test (id INT, 
                   field_1 INT, 
                   field_2 INT, 
                   field_3 INT,
                   PRIMARY KEY (id));
CREATE INDEX idx ON test (field_1);
WITH RECURSIVE
cte AS (
    SELECT 1 id
    UNION ALL
    SELECT id + 1 FROM cte WHERE id < 1000
)
INSERT INTO test
SELECT id, RANDOM() * 100, RANDOM() * 50, RANDOM() * 40
FROM cte;
1000 rows affected

explain (analyze,buffers)
SELECT COUNT(*)
FROM test t1
JOIN test t2 ON t1.field_1 = t2.field_2 + t2.field_3 + 10;

QUERY PLAN

Aggregate  (cost=623.82..623.83 rows=1 width=8) (actual time=3.876..3.878 rows=1 loops=1)

  Buffers: shared hit=482

  ->  Merge Join  (cost=129.17..581.04 rows=17112 width=0) (actual time=0.600..3.209 rows=9952 loops=1)

        Merge Cond: (t1.field_1 = (((t2.field_2 + t2.field_3) + 10)))

        Buffers: shared hit=482

        ->  Index Only Scan using idx on test t1  (cost=0.28..96.03 rows=1850 width=4) (actual time=0.033..0.366 rows=983 loops=1)

              Heap Fetches: 983

              Buffers: shared hit=470

        ->  Sort  (cost=128.89..133.52 rows=1850 width=8) (actual time=0.514..1.160 rows=9941 loops=1)

              Sort Key: (((t2.field_2 + t2.field_3) + 10))

              Sort Method: quicksort  Memory: 71kB

              Buffers: shared hit=12

              ->  Seq Scan on test t2  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.017..0.191 rows=1000 loops=1)

                    Buffers: shared hit=6

Planning Time: 0.552 ms

Execution Time: 4.005 ms

explain (analyze,buffers)
SELECT COUNT(*)
FROM test t1
JOIN test t2 ON t2.field_2 + t2.field_3 + 10 = t1.field_1;

QUERY PLAN

Aggregate  (cost=623.82..623.83 rows=1 width=8) (actual time=3.718..3.719 rows=1 loops=1)

  Buffers: shared hit=476

  ->  Merge Join  (cost=129.17..581.04 rows=17112 width=0) (actual time=0.439..3.023 rows=9952 loops=1)

        Merge Cond: (t1.field_1 = (((t2.field_2 + t2.field_3) + 10)))

        Buffers: shared hit=476

        ->  Index Only Scan using idx on test t1  (cost=0.28..96.03 rows=1850 width=4) (actual time=0.025..0.340 rows=983 loops=1)

              Heap Fetches: 983

              Buffers: shared hit=470

        ->  Sort  (cost=128.89..133.52 rows=1850 width=8) (actual time=0.372..1.028 rows=9941 loops=1)

              Sort Key: (((t2.field_2 + t2.field_3) + 10))

              Sort Method: quicksort  Memory: 71kB

              Buffers: shared hit=6

              ->  Seq Scan on test t2  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.011..0.177 rows=1000 loops=1)

                    Buffers: shared hit=6

Planning Time: 0.098 ms

Execution Time: 3.747 ms