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,
  add_bis TEXT GENERATED ALWAYS AS (REGEXP_REPLACE(address, 'The Building', 'The BUILDING')) STORED,
  p_c_bis TEXT GENERATED ALWAYS AS (REGEXP_REPLACE(post_code, 'abc', 'ABC')) STORED
);

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 add_bis_ix ON test (add_bis);

CREATE INDEX

CREATE INDEX p_c_bis_ix ON test (p_c_bis);

CREATE INDEX

-- SELECT * FROM test;
SET enable_seqscan = OFF;

SET

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

QUERY PLAN

Index Scan using add_bis_ix on public.test  (cost=0.13..8.15 rows=1 width=132) (actual time=0.034..0.034 rows=0 loops=1)

  Output: t_id, address, post_code, add_bis, p_c_bis

  Index Cond: (test.add_bis = 'The BUILDING%'::text)

  Buffers: shared read=1

Planning:

  Buffers: shared hit=40 read=2

Planning Time: 0.873 ms

Execution Time: 0.059 ms

EXPLAIN

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

QUERY PLAN

Index Only Scan using p_c_bis_ix on public.test  (cost=0.13..8.15 rows=1 width=32) (actual time=0.198..0.199 rows=1 loops=1)

  Output: p_c_bis

  Index Cond: (test.p_c_bis = 'ABC123'::text)

  Heap Fetches: 1

  Buffers: shared hit=1 read=1

Planning Time: 0.049 ms

Execution Time: 0.212 ms

EXPLAIN

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

QUERY PLAN

Index Only Scan using add_bis_ix on public.test  (cost=0.13..12.18 rows=1 width=32) (actual time=0.007..0.009 rows=3 loops=1)

  Output: add_bis

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

  Heap Fetches: 3

  Buffers: shared hit=2

Planning:

  Buffers: shared hit=9

Planning Time: 0.130 ms

Execution Time: 0.021 ms

EXPLAIN

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

QUERY PLAN

Seq Scan on public.test  (cost=10000000000.00..10000000001.04 rows=1 width=132) (actual time=0.009..0.010 rows=3 loops=1)

  Output: t_id, address, post_code, add_bis, p_c_bis

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

  Buffers: shared hit=1

Planning Time: 0.044 ms

Execution Time: 0.030 ms

EXPLAIN

SELECT
  REGEXP_REPLACE('Flat 25 Arliss Court 24', E'\\_|\\W','','g');

regexp_replace

Flat25ArlissCourt24

SELECT 1

SELECT
  REGEXP_REPLACE('Flat 25 Arliss Court 24', '_|\W','','g');

regexp_replace

Flat25ArlissCourt24

SELECT 1