Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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.056..0.057 rows=0 loops=1)

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

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

  Heap Fetches: 0

Planning Time: 0.477 ms

Execution Time: 0.122 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.008..0.008 rows=0 loops=1)

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

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

  Heap Fetches: 0

Planning Time: 0.145 ms

Execution Time: 0.026 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.068 ms

Execution Time: 0.016 ms

EXPLAIN