SELECT version();

CREATE TABLE parent ( x INTEGER PRIMARY KEY, -- presumably IDENTITY or SERIAL... y TEXT NOT NULL -- other fields -- );

INSERT INTO parent SELECT s, s::TEXT || ' PK text' FROM GENERATE_SERIES(1, 100) AS r(s);

SELECT * FROM parent;

CREATE TABLE child ( b TEXT, -- -- other fields t TEXT );

SELECT n.t, n.t::TEXT || ' text part', GENERATE_SERIES (1, 10) FROM GENERATE_SERIES(1, 5) AS n(t);

SELECT GENERATE_SERIES (1, 10) AS m, n.t, n.t::TEXT || ' text part!' FROM GENERATE_SERIES(1, 5) AS n(t);

INSERT INTO child SELECT GENERATE_SERIES (1, 100) AS m, n.t::TEXT || ' text part!' FROM GENERATE_SERIES(1, 5) AS n(t);

SELECT * FROM child;

SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::int WHERE pa.x = 23;

EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE pa.x = 23;

EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE REPLACE(ch.b, 'Ref:', '')::INT = 23;

EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE pa.x = 23;

-- -- Why so many brackets? -- CREATE INDEX ch_b_replace_ix_bis ON child (((REPLACE(b, 'Ref:', ''))::INT));

-- -- Fails! -- -- CREATE INDEX ch_b_replace_ix_ter ON child ((REPLACE(b, 'Ref:', ''))::INT)

-- -- Works! -- -- CREATE INDEX ch_b_replace_ix ON child (CAST(REPLACE(b, 'Ref:', '') AS INT));

EXPLAIN (ANALYZE, TIMING OFF) SELECT pa.x, pa.y, ch.b, ch.t FROM parent pa JOIN child ch ON pa.x = REPLACE(ch.b, 'Ref:', '')::INT WHERE pa.x = 23;