Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Table with additional columns (col2 - col8 only to simulate aditional space requirements of table)
CREATE TABLE tbl (tbl_id serial, col text, col2 text, col3 text, col4 text
                                         , col5 text, col6 text, col7 text, col8 text);

INSERT INTO tbl (col, col2, col3, col4, col5, col6, col7, col8)
SELECT txt, txt, txt, txt, txt, txt, txt, txt
FROM  (
	SELECT string_agg(c, '') AS txt                   -- aggregate random strings
	FROM  (
		SELECT i, chr((random() * 80)::int + 43) AS c -- 43-123 = common range of characters
		FROM   generate_series(1,1000000) i
		) sub
	GROUP  BY i%100000
	) sub2;

DELETE FROM tbl where random() > 0.95;                -- some dead rows

ANALYZE tbl;

-- CREATE INDEX tbl_col_idx ON tbl (col);             -- would be useless!
CREATE INDEX tbl_col_ops_idx ON tbl (col text_pattern_ops);
CREATE INDEX tbl_col_left3_idx ON tbl (left(col, 3));

CREATE TABLE

INSERT 0 100000

DELETE 5056

ANALYZE

CREATE INDEX

CREATE INDEX

-- Find existing combination  ...
-- SELECT * FROM tbl WHERE col LIKE 'Br%';
-- uses tbl_col_ops_idx
-- Run several times to exclude saching effects
EXPLAIN ANALYZE SELECT * FROM tbl WHERE col LIKE 'Bra%';
EXPLAIN ANALYZE SELECT * FROM tbl WHERE col LIKE '0ra%';
EXPLAIN ANALYZE SELECT * FROM tbl WHERE col LIKE 'zra%';
EXPLAIN ANALYZE SELECT * FROM tbl WHERE col LIKE '-ra%';

QUERY PLAN

Index Scan using tbl_col_ops_idx on tbl  (cost=0.42..8.44 rows=9 width=92) (actual time=0.079..0.080 rows=0 loops=1)

  Index Cond: ((col ~>=~ 'Bra'::text) AND (col ~<~ 'Brb'::text))

  Filter: (col ~~ 'Bra%'::text)

Planning Time: 0.612 ms

Execution Time: 0.166 ms

EXPLAIN

QUERY PLAN

Index Scan using tbl_col_ops_idx on tbl  (cost=0.42..8.44 rows=9 width=92) (actual time=0.038..0.038 rows=0 loops=1)

  Index Cond: ((col ~>=~ '0ra'::text) AND (col ~<~ '0rb'::text))

  Filter: (col ~~ '0ra%'::text)

Planning Time: 0.087 ms

Execution Time: 0.052 ms

EXPLAIN

QUERY PLAN

Index Scan using tbl_col_ops_idx on tbl  (cost=0.42..8.44 rows=9 width=92) (actual time=0.056..0.056 rows=0 loops=1)

  Index Cond: ((col ~>=~ 'zra'::text) AND (col ~<~ 'zrb'::text))

  Filter: (col ~~ 'zra%'::text)

Planning Time: 0.072 ms

Execution Time: 0.069 ms

EXPLAIN

QUERY PLAN

Index Scan using tbl_col_ops_idx on tbl  (cost=0.42..8.44 rows=9 width=92) (actual time=0.037..0.037 rows=0 loops=1)

  Index Cond: ((col ~>=~ '-ra'::text) AND (col ~<~ '-rb'::text))

  Filter: (col ~~ '-ra%'::text)

Planning Time: 0.071 ms

Execution Time: 0.050 ms

EXPLAIN

-- uses tbl_col3_idx;
-- Run several times to exclude saching effects
EXPLAIN ANALYZE SELECT * FROM tbl WHERE left(col, 3) = 'Bra';
EXPLAIN ANALYZE SELECT * FROM tbl WHERE left(col, 3) = '0ra';
EXPLAIN ANALYZE SELECT * FROM tbl WHERE left(col, 3) = 'zra';
EXPLAIN ANALYZE SELECT * FROM tbl WHERE left(col, 3) = '-ra';

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=11.97..1027.59 rows=475 width=92) (actual time=0.075..0.075 rows=0 loops=1)

  Recheck Cond: ("left"(col, 3) = 'Bra'::text)

  ->  Bitmap Index Scan on tbl_col_left3_idx  (cost=0.00..11.86 rows=475 width=0) (actual time=0.069..0.069 rows=0 loops=1)

        Index Cond: ("left"(col, 3) = 'Bra'::text)

Planning Time: 0.097 ms

Execution Time: 0.108 ms

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=11.97..1027.59 rows=475 width=92) (actual time=0.381..0.381 rows=0 loops=1)

  Recheck Cond: ("left"(col, 3) = '0ra'::text)

  ->  Bitmap Index Scan on tbl_col_left3_idx  (cost=0.00..11.86 rows=475 width=0) (actual time=0.379..0.380 rows=0 loops=1)

        Index Cond: ("left"(col, 3) = '0ra'::text)

Planning Time: 0.051 ms

Execution Time: 0.402 ms

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=11.97..1027.59 rows=475 width=92) (actual time=0.040..0.041 rows=0 loops=1)

  Recheck Cond: ("left"(col, 3) = 'zra'::text)

  ->  Bitmap Index Scan on tbl_col_left3_idx  (cost=0.00..11.86 rows=475 width=0) (actual time=0.039..0.039 rows=0 loops=1)

        Index Cond: ("left"(col, 3) = 'zra'::text)

Planning Time: 0.052 ms

Execution Time: 0.061 ms

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=11.97..1027.59 rows=475 width=92) (actual time=0.041..0.042 rows=0 loops=1)

  Recheck Cond: ("left"(col, 3) = '-ra'::text)

  ->  Bitmap Index Scan on tbl_col_left3_idx  (cost=0.00..11.86 rows=475 width=0) (actual time=0.040..0.040 rows=0 loops=1)

        Index Cond: ("left"(col, 3) = '-ra'::text)

Planning Time: 0.049 ms

Execution Time: 0.061 ms

EXPLAIN