Postgres 15

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 words (
            id bigint generated always as identity primary key
            , word text not null
            );

CREATE TABLE

insert into words(word) select random() from generate_series(1,100000);

INSERT 0 100000

vacuum analyze words;

VACUUM

--set enable_seqscan=off;
create index words_word on words (word text_pattern_ops);

CREATE INDEX

explain (analyze, costs off)
           select word from words where word like 'data%';

QUERY PLAN

Index Only Scan using words_word on words (actual time=0.041..0.042 rows=0 loops=1)

  Index Cond: ((word ~>=~ 'data'::text) AND (word ~<~ 'datb'::text))

  Filter: (word ~~ 'data%'::text)

  Heap Fetches: 0

Planning Time: 0.527 ms

Execution Time: 0.105 ms

EXPLAIN

vacuum words;

VACUUM

explain (analyze, costs off)
           select word from words where word ^@ 'data';

QUERY PLAN

Index Only Scan using words_word on words (actual time=0.007..0.007 rows=0 loops=1)

  Index Cond: ((word ~>=~ 'data'::text) AND (word ~<~ 'datb'::text))

  Filter: (word ^@ 'data'::text)

  Heap Fetches: 0

Planning Time: 0.140 ms

Execution Time: 0.024 ms

EXPLAIN

explain (analyze, costs off)
           select word from words where starts_with(word,'data');

QUERY PLAN

Index Only Scan using words_word on words (actual time=0.006..0.006 rows=0 loops=1)

  Index Cond: ((word ~>=~ 'data'::text) AND (word ~<~ 'datb'::text))

  Filter: starts_with(word, 'data'::text)

  Heap Fetches: 0

Planning Time: 0.069 ms

Execution Time: 0.017 ms

EXPLAIN