Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE TABLE test
(
  t_id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  address TEXT,
  post_code TEXT
);

CREATE TABLE

INSERT INTO test (address, post_code) VALUES
('The Building, Apt 13, Flr 6', 'abc123'),
('The Building, Apt 45, Flr 8', 'abc456'),
('The Building, Apt 45, Flr 9', 'abc789');

INSERT 0 3

-- CREATE INDEX address_ix ON test (address);
-- CREATE INDEX post_code_ix ON test (post_code);
CREATE INDEX my_field_regexp_idx ON test (REGEXP_REPLACE(address, 'The Building', 'The BUILDING'));

CREATE INDEX

CREATE INDEX post_code_regexp_idx ON test(REGEXP_REPLACE(post_code, 'abc', 'ABC'));

CREATE INDEX

SELECT * FROM test;

t_id
address
post_code

1
The Building, Apt 13, Flr 6
abc123

2
The Building, Apt 45, Flr 8
abc456

3
The Building, Apt 45, Flr 9
abc789

SELECT 3

SET enable_seqscan = OFF;

SET

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
  *
FROM
  test
WHERE address LIKE 'The Building';

QUERY PLAN

Seq Scan on public.test  (cost=10000000000.00..10000000001.04 rows=1 width=68) (actual time=0.011..0.011 rows=0 loops=1)

  Output: t_id, address, post_code

  Filter: (test.address ~~ 'The Building'::text)

  Rows Removed by Filter: 3

  Buffers: shared hit=1

Planning Time: 0.065 ms

Execution Time: 0.034 ms

EXPLAIN

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
  post_code
FROM test
WHERE post_code = 'ABC123';

QUERY PLAN

Seq Scan on public.test  (cost=10000000000.00..10000000001.04 rows=1 width=32) (actual time=0.011..0.012 rows=0 loops=1)

  Output: post_code

  Filter: (test.post_code = 'ABC123'::text)

  Rows Removed by Filter: 3

  Buffers: shared hit=1

Planning:

  Buffers: shared hit=12

Planning Time: 0.304 ms

Execution Time: 0.024 ms

EXPLAIN

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
  address
FROM test
WHERE address LIKE 'The BUILDING%';

QUERY PLAN

Seq Scan on public.test  (cost=10000000000.00..10000000001.04 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=1)

  Output: address

  Filter: (test.address ~~ 'The BUILDING%'::text)

  Rows Removed by Filter: 3

  Buffers: shared hit=1

Planning:

  Buffers: shared hit=3

Planning Time: 0.063 ms

Execution Time: 0.018 ms

EXPLAIN

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
  *
FROM test
WHERE address LIKE 'The BUILDING%';

QUERY PLAN

Seq Scan on public.test  (cost=10000000000.00..10000000001.04 rows=1 width=68) (actual time=0.008..0.009 rows=0 loops=1)

  Output: t_id, address, post_code

  Filter: (test.address ~~ 'The BUILDING%'::text)

  Rows Removed by Filter: 3

  Buffers: shared hit=1

Planning Time: 0.041 ms

Execution Time: 0.017 ms

EXPLAIN
t_id	address	post_code
1	The Building, Apt 13, Flr 6	abc123
2	The Building, Apt 45, Flr 8	abc456
3	The Building, Apt 45, Flr 9	abc789