Postgres 13

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 records (
  id         bigint PRIMARY KEY               
, moves      varchar(255)[]  -- should be text[]
, evaluation varchar(255)[]  -- should be text[]
);

INSERT INTO records(id, moves, evaluation) VALUES
  (1, '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}', '{1,2,3,4,5,6,7,8,9,10}')
, (2, '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h6}', '{2,2,3,4,5,6,7,8,9,11}')
, (3, '{a4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h6}', '{3,2,3,4,5,6,7,8,9,12}');
3 rows affected

-- No superuser privileges on dbfiddle!
/*
CREATE OR REPLACE FUNCTION public.array_to_string_immutable(text[], text)
 RETURNS text
 LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
AS 'array_to_text'

CREATE OR REPLACE FUNCTION public.f_8moves(text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.array_to_string_immutable($1[1:8], '')
$func$;
*/
-- Fall back to next-best option:
CREATE OR REPLACE FUNCTION public.f_8moves(text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT array_to_string($1[1:8], '')
$func$;
CREATE INDEX records_8moves_idx ON records (public.f_8moves(moves) COLLATE "C");
VACUUM ANALYZE records;
-- First filter with index scan
SELECT *
FROM   records
WHERE  public.f_8moves(moves) = public.f_8moves('{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}') COLLATE "C";


id
moves
evaluation


1
{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}
{1,2,3,4,5,6,7,8,9,10}

2
{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h6}
{2,2,3,4,5,6,7,8,9,11}


-- Then narrow down with exact condition
SELECT *
FROM   records
WHERE  public.f_8moves(moves) = public.f_8moves('{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}') COLLATE "C"
AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'
AND    evaluation IS NOT NULL;


id
moves
evaluation


1
{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}
{1,2,3,4,5,6,7,8,9,10}


SET enable_seqscan = off;  -- force index for small table
EXPLAIN ANALYZE
SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) = public.f_8moves('{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}') COLLATE "C"
AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'
AND    evaluation IS NOT NULL;


QUERY PLAN


Index Scan using records_8moves_idx on records  (cost=0.13..12.17 rows=1 width=516) (actual time=0.021..0.024 rows=1 loops=1)

  Index Cond: (f_8moves((moves)::text[]) = 'b4e5Bb2d6Nf3Nf6g3Bg4'::text COLLATE "C")

  Filter: ((evaluation IS NOT NULL) AND (moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'::character varying[]))

  Rows Removed by Filter: 1

Planning Time: 0.107 ms

Execution Time: 0.048 ms


EXPLAIN ANALYZE
SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) = public.f_8moves('{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}') COLLATE "C"
AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'
AND    evaluation IS NOT NULL;


QUERY PLAN


Index Scan using records_8moves_idx on records  (cost=0.13..12.17 rows=1 width=516) (actual time=0.010..0.012 rows=1 loops=1)

  Index Cond: (f_8moves((moves)::text[]) = 'b4e5Bb2d6Nf3Nf6g3Bg4'::text COLLATE "C")

  Filter: ((evaluation IS NOT NULL) AND (moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'::character varying[]))

  Rows Removed by Filter: 1

Planning Time: 0.094 ms

Execution Time: 0.024 ms


EXPLAIN ANALYZE
SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) LIKE (public.f_8moves('{b4,e5}') || '%') COLLATE "C"  -- COLLATE "C" is optional for LIKE
AND    moves[1:2] = '{b4,e5}'
AND    evaluation IS NOT NULL;


QUERY PLAN


Index Scan using records_8moves_idx on records  (cost=0.13..12.68 rows=1 width=516) (actual time=0.041..0.049 rows=2 loops=1)

  Index Cond: ((f_8moves((moves)::text[]) >= 'b4e5'::text) AND (f_8moves((moves)::text[]) < 'b4e6'::text))

  Filter: ((evaluation IS NOT NULL) AND (moves[1:2] = '{b4,e5}'::character varying[]) AND (f_8moves((moves)::text[]) ~~ 'b4e5%'::text COLLATE "C"))

Planning Time: 0.198 ms

Execution Time: 0.063 ms
id	moves	evaluation
1	{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}	{1,2,3,4,5,6,7,8,9,10}
2	{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h6}	{2,2,3,4,5,6,7,8,9,11}