Postgres 16

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 medical_codes (code text NOT NULL);
INSERT INTO medical_codes VALUES
  ('A04')
, ('A04.1')
, ('A04.2')
, ('A05')
, ('A06')
;

-- ~ 55k more random rows
INSERT INTO medical_codes
WITH cte AS(
   SELECT md5(g::text) AS code
   FROM   generate_series(1,20000) g
   )
SELECT left(code, 3) FROM cte
UNION ALL
SELECT left(code, l)
FROM   cte, generate_series(4,8) l
WHERE random() > .65;

-- SP-GiST index !!!!!
CREATE INDEX medical_codes_code_spgist_idx ON medical_codes USING spgist(code);

CREATE TABLE

INSERT 0 5

INSERT 0 54969

CREATE INDEX

VACUUM ANALYZE medical_codes;

VACUUM

-- for given set of prefixes
WITH prefix(code) AS (
   VALUES
     ('A04')
   , ('A04.1')
   , ('A04.2')
   , ('A05')
   , ('A06')
   )
SELECT p.code AS prefix, c.code
FROM   prefix p
LEFT   JOIN medical_codes c ON c.code ^@ p.code
ORDER  BY 1, 2;  -- optional

prefix
code

A04
A04

A04
A04.1

A04
A04.2

A04.1
A04.1

A04.2
A04.2

A05
A05

A06
A06

SELECT 7

-- query plan for given set of prefixes
EXPLAIN ANALYZE
WITH prefix(code) AS (
   VALUES
     ('A04')
   , ('A04.1')
   , ('A04.2')
   , ('A05')
   , ('A06')
   )
SELECT p.code AS prefix, c.code
FROM   prefix p
LEFT   JOIN medical_codes c ON c.code ^@ p.code
ORDER  BY 1, 2;  -- optional

QUERY PLAN

Sort  (cost=150.88..154.31 rows=1374 width=37) (actual time=0.047..0.048 rows=7 loops=1)

  Sort Key: "*VALUES*".column1, c.code

  Sort Method: quicksort  Memory: 25kB

  ->  Nested Loop Left Join  (cost=0.28..79.26 rows=1374 width=37) (actual time=0.018..0.033 rows=7 loops=1)

        ->  Values Scan on "*VALUES*"  (cost=0.00..0.06 rows=5 width=32) (actual time=0.005..0.007 rows=5 loops=1)

        ->  Index Only Scan using medical_codes_code_spgist_idx on medical_codes c  (cost=0.28..13.09 rows=275 width=5) (actual time=0.004..0.004 rows=1 loops=5)

              Index Cond: (code ^@ "*VALUES*".column1)

              Heap Fetches: 0

Planning Time: 0.081 ms

Execution Time: 0.068 ms

EXPLAIN

-- even does an index-only scan for the whole table
-- (which would seem like expensive nonsense)
EXPLAIN ANALYZE
SELECT p.code AS prefix, c.code
FROM   medical_codes p
LEFT   JOIN medical_codes c ON c.code ^@ p.code
ORDER  BY 1, 2;  -- optional

QUERY PLAN

Sort  (cost=2751126.77..2788903.53 rows=15110703 width=10) (actual time=2164.402..2459.079 rows=385941 loops=1)

  Sort Key: p.code, c.code

  Sort Method: external merge  Disk: 7800kB

  ->  Nested Loop Left Join  (cost=0.28..432764.90 rows=15110703 width=10) (actual time=397.609..1204.629 rows=385941 loops=1)

        ->  Seq Scan on medical_codes p  (cost=0.00..800.74 rows=54974 width=5) (actual time=0.016..26.644 rows=54974 loops=1)

        ->  Index Only Scan using medical_codes_code_spgist_idx on medical_codes c  (cost=0.28..5.11 rows=275 width=5) (actual time=0.010..0.012 rows=7 loops=54974)

              Index Cond: (code ^@ p.code)

              Heap Fetches: 0

Planning Time: 0.089 ms

JIT:

  Functions: 4

  Options: Inlining true, Optimization true, Expressions true, Deforming true

  Timing: Generation 0.519 ms, Inlining 280.996 ms, Optimization 68.604 ms, Emission 47.939 ms, Total 398.059 ms

Execution Time: 2848.998 ms

EXPLAIN

-- same without JIT
SET jit = off;

EXPLAIN ANALYZE
SELECT p.code AS prefix, c.code
FROM   medical_codes p
LEFT   JOIN medical_codes c ON c.code ^@ p.code
ORDER  BY 1, 2;  -- optional

SET

QUERY PLAN

Sort  (cost=2751126.77..2788903.53 rows=15110703 width=10) (actual time=1651.891..1930.406 rows=385941 loops=1)

  Sort Key: p.code, c.code

  Sort Method: external merge  Disk: 7800kB

  ->  Nested Loop Left Join  (cost=0.28..432764.90 rows=15110703 width=10) (actual time=0.030..775.433 rows=385941 loops=1)

        ->  Seq Scan on medical_codes p  (cost=0.00..800.74 rows=54974 width=5) (actual time=0.011..10.926 rows=54974 loops=1)

        ->  Index Only Scan using medical_codes_code_spgist_idx on medical_codes c  (cost=0.28..5.11 rows=275 width=5) (actual time=0.010..0.012 rows=7 loops=54974)

              Index Cond: (code ^@ p.code)

              Heap Fetches: 0

Planning Time: 0.121 ms

Execution Time: 1963.604 ms

EXPLAIN