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 table1(
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, col1 text
, col1_tsv tsvector
);

INSERT INTO table1 (col1, col1_tsv)
VALUES ('Blacklist', TO_TSVECTOR('Blacklist'))
     , ('Cheesecake', TO_TSVECTOR('Cheesecake'));

-- original ts index
CREATE INDEX table1_col1_index ON table1 USING gin(col1_tsv);

-- ts index on reversed strings
CREATE INDEX table1_col1_rtsv_idx ON table1 USING gin (to_tsvector('simple', reverse(col1)));

-- trigram index
CREATE INDEX table1_col1_gin_trgm_idx ON table1 USING gin (col1 gin_trgm_ops);

CREATE TABLE

INSERT 0 2

CREATE INDEX

CREATE INDEX

CREATE INDEX

-- to get index scans for minimal table if possible
set enable_seqscan = off;

SET

-- can use your org index
EXPLAIN
SELECT col1 FROM table1 WHERE col1_tsv @@ to_tsquery( 'blacklist');

QUERY PLAN

Bitmap Heap Scan on table1  (cost=8.25..12.51 rows=1 width=32)

  Recheck Cond: (col1_tsv @@ to_tsquery('blacklist'::text))

  ->  Bitmap Index Scan on table1_col1_index  (cost=0.00..8.25 rows=1 width=0)

        Index Cond: (col1_tsv @@ to_tsquery('blacklist'::text))

EXPLAIN

-- can use the reverse ts index:
EXPLAIN
SELECT col1 FROM table1 WHERE to_tsvector('simple', reverse(col1))
                           @@ to_tsquery('simple', reverse('cake') || ':*');

QUERY PLAN

Bitmap Heap Scan on table1  (cost=12.00..16.27 rows=1 width=32)

  Recheck Cond: (to_tsvector('simple'::regconfig, reverse(col1)) @@ '''ekac'':*'::tsquery)

  ->  Bitmap Index Scan on table1_col1_rtsv_idx  (cost=0.00..12.00 rows=1 width=0)

        Index Cond: (to_tsvector('simple'::regconfig, reverse(col1)) @@ '''ekac'':*'::tsquery)

EXPLAIN

-- trigram index supports all (less efficient, though):
EXPLAIN SELECT col1 FROM table1 WHERE col1 ILIKE 'blacklist';
EXPLAIN SELECT col1 FROM table1 WHERE col1 LIKE '%cake';
EXPLAIN SELECT col1 FROM table1 WHERE col1 LIKE '%cake%';

QUERY PLAN

Bitmap Heap Scan on table1  (cost=44.00..48.01 rows=1 width=32)

  Recheck Cond: (col1 ~~* 'blacklist'::text)

  ->  Bitmap Index Scan on table1_col1_gin_trgm_idx  (cost=0.00..44.00 rows=1 width=0)

        Index Cond: (col1 ~~* 'blacklist'::text)

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on table1  (cost=16.00..20.01 rows=1 width=32)

  Recheck Cond: (col1 ~~ '%cake'::text)

  ->  Bitmap Index Scan on table1_col1_gin_trgm_idx  (cost=0.00..16.00 rows=1 width=0)

        Index Cond: (col1 ~~ '%cake'::text)

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on table1  (cost=12.00..16.01 rows=1 width=32)

  Recheck Cond: (col1 ~~ '%cake%'::text)

  ->  Bitmap Index Scan on table1_col1_gin_trgm_idx  (cost=0.00..12.00 rows=1 width=0)

        Index Cond: (col1 ~~ '%cake%'::text)

EXPLAIN