Postgres 14

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 55127



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=151.25..154.70 rows=1378 width=37) (actual time=0.038..0.040 rows=7 loops=1)

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

  Sort Method: quicksort  Memory: 25kB

  ->  Nested Loop Left Join  (cost=0.28..79.40 rows=1378 width=37) (actual time=0.014..0.029 rows=7 loops=1)

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

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

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

              Heap Fetches: 0

Planning Time: 0.085 ms

Execution Time: 0.061 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=2767847.90..2805842.12 rows=15197687 width=10) (actual time=1504.437..1889.250 rows=388283 loops=1)

  Sort Key: p.code, c.code

  Sort Method: external merge  Disk: 7848kB

  ->  Nested Loop Left Join  (cost=0.28..435509.33 rows=15197687 width=10) (actual time=0.023..542.787 rows=388283 loops=1)

        ->  Seq Scan on medical_codes p  (cost=0.00..802.32 rows=55132 width=5) (actual time=0.010..11.420 rows=55132 loops=1)

        ->  Index Only Scan using medical_codes_code_spgist_idx on medical_codes c  (cost=0.28..5.12 rows=276 width=5) (actual time=0.007..0.008 rows=7 loops=55132)

              Index Cond: (code ^@ p.code)

              Heap Fetches: 0

Planning Time: 0.094 ms

Execution Time: 1921.236 ms



EXPLAIN