Postgres 9.6

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 lookup (
	surrogate_key BIGINT PRIMARY KEY,
	natural_key   TEXT NOT NULL UNIQUE,
	data	      TEXT NOT NULL);
	
INSERT INTO lookup
SELECT id, 'nk'||id, random()::text
FROM generate_series(1,400000) id;

CREATE UNIQUE INDEX lookup_ix ON lookup(natural_key, surrogate_key);
400000 rows affected

VACUUM ANALYSE lookup;
CREATE TABLE target (
	target_id	     BIGINT PRIMARY KEY,
	lookup_natural_key   TEXT NOT NULL,
	lookup_surrogate_key BIGINT,
	data		     TEXT NOT NULL
);

INSERT INTO target (target_id, lookup_natural_key, data)
SELECT id+1000, 'nk'||id, random()::text
FROM generate_series(1,1000) id;

ANALYSE target;
1000 rows affected

EXPLAIN (ANALYSE, VERBOSE, BUFFERS)
UPDATE target
SET lookup_surrogate_key = surrogate_key
FROM lookup
WHERE lookup_natural_key = natural_key;


QUERY PLAN


Update on fiddle_essghtmrfsvqhnjzxlby.target  (cost=0.42..7108.00 rows=1000 width=50) (actual time=23.578..23.578 rows=0 loops=1)

  Buffers: shared hit=8475 read=588 dirtied=15 written=220

  ->  Nested Loop  (cost=0.42..7108.00 rows=1000 width=50) (actual time=0.030..16.712 rows=1000 loops=1)

        Output: target.target_id, target.lookup_natural_key, lookup.surrogate_key, target.data, target.ctid, lookup.ctid

        Buffers: shared hit=3440 read=573 written=214

        ->  Seq Scan on fiddle_essghtmrfsvqhnjzxlby.target  (cost=0.00..18.00 rows=1000 width=36) (actual time=0.015..0.426 rows=1000 loops=1)

              Output: target.target_id, target.lookup_natural_key, target.data, target.ctid

              Buffers: shared hit=8

        ->  Index Scan using lookup_ix on fiddle_essghtmrfsvqhnjzxlby.lookup  (cost=0.42..7.08 rows=1 width=22) (actual time=0.015..0.015 rows=1 loops=1000)

              Output: lookup.surrogate_key, lookup.ctid, lookup.natural_key

              Index Cond: (lookup.natural_key = target.lookup_natural_key)

              Buffers: shared hit=3432 read=573 written=214

Planning time: 0.658 ms

Execution time: 23.615 ms


TRUNCATE target;

INSERT INTO target (target_id, lookup_natural_key, data)
SELECT id+1000, 'nk'||id, random()::text
FROM generate_series(1,1000) id;

ANALYSE target;
1000 rows affected

EXPLAIN (ANALYSE, VERBOSE, BUFFERS)
UPDATE target
SET lookup_surrogate_key = (
	SELECT surrogate_key
	FROM lookup
	WHERE lookup_natural_key = natural_key);


QUERY PLAN


Update on fiddle_essghtmrfsvqhnjzxlby.target  (cost=0.00..4458.00 rows=1000 width=44) (actual time=17.586..17.587 rows=0 loops=1)

  Buffers: shared hit=8049 read=15 dirtied=15

  ->  Seq Scan on fiddle_essghtmrfsvqhnjzxlby.target  (cost=0.00..4458.00 rows=1000 width=44) (actual time=0.057..10.721 rows=1000 loops=1)

        Output: target.target_id, target.lookup_natural_key, (SubPlan 1), target.data, target.ctid

        Buffers: shared hit=3014

        SubPlan 1

          ->  Index Only Scan using lookup_ix on fiddle_essghtmrfsvqhnjzxlby.lookup  (cost=0.42..4.44 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1000)

                Output: lookup.surrogate_key

                Index Cond: (lookup.natural_key = target.lookup_natural_key)

                Heap Fetches: 0

                Buffers: shared hit=3006

Planning time: 0.304 ms

Execution time: 17.628 ms