Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > SELECT version(); > > <pre> > | version | > | :----------------------------------------------------------------------------------------------------- | > | PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit | > </pre> <!-- --> > CREATE TABLE parent > ( > x INTEGER PRIMARY KEY, -- presumably IDENTITY or SERIAL... > y TEXT NOT NULL > -- other fields > -- > ); > > <pre> > ✓ > </pre> <!-- --> > INSERT INTO parent > SELECT s, s::TEXT || ' PK text' FROM GENERATE_SERIES(1, 100) AS r(s); > > <pre> 100 rows affected > </pre> <!-- --> > SELECT * FROM parent; > > <pre> > x | y > --: | :---------- > 1 | 1 PK text > 2 | 2 PK text > 3 | 3 PK text > 4 | 4 PK text > 5 | 5 PK text > 6 | 6 PK text > 7 | 7 PK text > 8 | 8 PK text > 9 | 9 PK text > 10 | 10 PK text > 11 | 11 PK text > 12 | 12 PK text > 13 | 13 PK text > 14 | 14 PK text > 15 | 15 PK text > 16 | 16 PK text > 17 | 17 PK text > 18 | 18 PK text > 19 | 19 PK text > 20 | 20 PK text > 21 | 21 PK text > 22 | 22 PK text > 23 | 23 PK text > 24 | 24 PK text > 25 | 25 PK text > 26 | 26 PK text > 27 | 27 PK text > 28 | 28 PK text > 29 | 29 PK text > 30 | 30 PK text > 31 | 31 PK text > 32 | 32 PK text > 33 | 33 PK text > 34 | 34 PK text > 35 | 35 PK text > 36 | 36 PK text > 37 | 37 PK text > 38 | 38 PK text > 39 | 39 PK text > 40 | 40 PK text > 41 | 41 PK text > 42 | 42 PK text > 43 | 43 PK text > 44 | 44 PK text > 45 | 45 PK text > 46 | 46 PK text > 47 | 47 PK text > 48 | 48 PK text > 49 | 49 PK text > 50 | 50 PK text > 51 | 51 PK text > 52 | 52 PK text > 53 | 53 PK text > 54 | 54 PK text > 55 | 55 PK text > 56 | 56 PK text > 57 | 57 PK text > 58 | 58 PK text > 59 | 59 PK text > 60 | 60 PK text > 61 | 61 PK text > 62 | 62 PK text > 63 | 63 PK text > 64 | 64 PK text > 65 | 65 PK text > 66 | 66 PK text > 67 | 67 PK text > 68 | 68 PK text > 69 | 69 PK text > 70 | 70 PK text > 71 | 71 PK text > 72 | 72 PK text > 73 | 73 PK text > 74 | 74 PK text > 75 | 75 PK text > 76 | 76 PK text > 77 | 77 PK text > 78 | 78 PK text > 79 | 79 PK text > 80 | 80 PK text > 81 | 81 PK text > 82 | 82 PK text > 83 | 83 PK text > 84 | 84 PK text > 85 | 85 PK text > 86 | 86 PK text > 87 | 87 PK text > 88 | 88 PK text > 89 | 89 PK text > 90 | 90 PK text > 91 | 91 PK text > 92 | 92 PK text > 93 | 93 PK text > 94 | 94 PK text > 95 | 95 PK text > 96 | 96 PK text > 97 | 97 PK text > 98 | 98 PK text > 99 | 99 PK text > 100 | 100 PK text > </pre> <!-- --> > CREATE TABLE child > ( > b TEXT, > -- > -- other fields > t TEXT > ); > > <pre> > ✓ > </pre> <!-- --> > SELECT n.t, n.t::TEXT || ' text part', GENERATE_SERIES (1, 10) > FROM GENERATE_SERIES(1, 5) AS n(t); > > <pre> > t | ?column? | generate_series > -: | :---------- | --------------: > 1 | 1 text part | 1 > 1 | 1 text part | 2 > 1 | 1 text part | 3 > 1 | 1 text part | 4 > 1 | 1 text part | 5 > 1 | 1 text part | 6 > 1 | 1 text part | 7 > 1 | 1 text part | 8 > 1 | 1 text part | 9 > 1 | 1 text part | 10 > 2 | 2 text part | 1 > 2 | 2 text part | 2 > 2 | 2 text part | 3 > 2 | 2 text part | 4 > 2 | 2 text part | 5 > 2 | 2 text part | 6 > 2 | 2 text part | 7 > 2 | 2 text part | 8 > 2 | 2 text part | 9 > 2 | 2 text part | 10 > 3 | 3 text part | 1 > 3 | 3 text part | 2 > 3 | 3 text part | 3 > 3 | 3 text part | 4 > 3 | 3 text part | 5 > 3 | 3 text part | 6 > 3 | 3 text part | 7 > 3 | 3 text part | 8 > 3 | 3 text part | 9 > 3 | 3 text part | 10 > 4 | 4 text part | 1 > 4 | 4 text part | 2 > 4 | 4 text part | 3 > 4 | 4 text part | 4 > 4 | 4 text part | 5 > 4 | 4 text part | 6 > 4 | 4 text part | 7 > 4 | 4 text part | 8 > 4 | 4 text part | 9 > 4 | 4 text part | 10 > 5 | 5 text part | 1 > 5 | 5 text part | 2 > 5 | 5 text part | 3 > 5 | 5 text part | 4 > 5 | 5 text part | 5 > 5 | 5 text part | 6 > 5 | 5 text part | 7 > 5 | 5 text part | 8 > 5 | 5 text part | 9 > 5 | 5 text part | 10 > </pre> <!-- --> > SELECT GENERATE_SERIES (1, 10) AS m, n.t, n.t::TEXT || ' text part!' > FROM GENERATE_SERIES(1, 5) AS n(t); > > <pre> > m | t | ?column? > -: | -: | :----------- > 1 | 1 | 1 text part! > 2 | 1 | 1 text part! > 3 | 1 | 1 text part! > 4 | 1 | 1 text part! > 5 | 1 | 1 text part! > 6 | 1 | 1 text part! > 7 | 1 | 1 text part! > 8 | 1 | 1 text part! > 9 | 1 | 1 text part! > 10 | 1 | 1 text part! > 1 | 2 | 2 text part! > 2 | 2 | 2 text part! > 3 | 2 | 2 text part! > 4 | 2 | 2 text part! > 5 | 2 | 2 text part! > 6 | 2 | 2 text part! > 7 | 2 | 2 text part! > 8 | 2 | 2 text part! > 9 | 2 | 2 text part! > 10 | 2 | 2 text part! > 1 | 3 | 3 text part! > 2 | 3 | 3 text part! > 3 | 3 | 3 text part! > 4 | 3 | 3 text part! > 5 | 3 | 3 text part! > 6 | 3 | 3 text part! > 7 | 3 | 3 text part! > 8 | 3 | 3 text part! > 9 | 3 | 3 text part! > 10 | 3 | 3 text part! > 1 | 4 | 4 text part! > 2 | 4 | 4 text part! > 3 | 4 | 4 text part! > 4 | 4 | 4 text part! > 5 | 4 | 4 text part! > 6 | 4 | 4 text part! > 7 | 4 | 4 text part! > 8 | 4 | 4 text part! > 9 | 4 | 4 text part! > 10 | 4 | 4 text part! > 1 | 5 | 5 text part! > 2 | 5 | 5 text part! > 3 | 5 | 5 text part! > 4 | 5 | 5 text part! > 5 | 5 | 5 text part! > 6 | 5 | 5 text part! > 7 | 5 | 5 text part! > 8 | 5 | 5 text part! > 9 | 5 | 5 text part! > 10 | 5 | 5 text part! > </pre> <!-- --> > INSERT INTO child > SELECT GENERATE_SERIES (1, 100) AS m, n.t::TEXT || ' text part!' > FROM GENERATE_SERIES(1, 5) AS n(t); > > <pre> 500 rows affected > </pre> <!-- --> > SELECT * FROM child; > > <pre> > b | t > :-- | :----------- > 1 | 1 text part! > 2 | 1 text part! > 3 | 1 text part! > 4 | 1 text part! > 5 | 1 text part! > 6 | 1 text part! > 7 | 1 text part! > 8 | 1 text part! > 9 | 1 text part! > 10 | 1 text part! > 11 | 1 text part! > 12 | 1 text part! > 13 | 1 text part! > 14 | 1 text part! > 15 | 1 text part! > 16 | 1 text part! > 17 | 1 text part! > 18 | 1 text part! > 19 | 1 text part! > 20 | 1 text part! > 21 | 1 text part! > 22 | 1 text part! > 23 | 1 text part! > 24 | 1 text part! > 25 | 1 text part! > 26 | 1 text part! > 27 | 1 text part! > 28 | 1 text part! > 29 | 1 text part! > 30 | 1 text part! > 31 | 1 text part! > 32 | 1 text part! > 33 | 1 text part! > 34 | 1 text part! > 35 | 1 text part! > 36 | 1 text part! > 37 | 1 text part! > 38 | 1 text part! > 39 | 1 text part! > 40 | 1 text part! > 41 | 1 text part! > 42 | 1 text part! > 43 | 1 text part! > 44 | 1 text part! > 45 | 1 text part! > 46 | 1 text part! > 47 | 1 text part! > 48 | 1 text part! > 49 | 1 text part! > 50 | 1 text part! > 51 | 1 text part! > 52 | 1 text part! > 53 | 1 text part! > 54 | 1 text part! > 55 | 1 text part! > 56 | 1 text part! > 57 | 1 text part! > 58 | 1 text part! > 59 | 1 text part! > 60 | 1 text part! > 61 | 1 text part! > 62 | 1 text part! > 63 | 1 text part! > 64 | 1 text part! > 65 | 1 text part! > 66 | 1 text part! > 67 | 1 text part! > 68 | 1 text part! > 69 | 1 text part! > 70 | 1 text part! > 71 | 1 text part! > 72 | 1 text part! > 73 | 1 text part! > 74 | 1 text part! > 75 | 1 text part! > 76 | 1 text part! > 77 | 1 text part! > 78 | 1 text part! > 79 | 1 text part! > 80 | 1 text part! > 81 | 1 text part! > 82 | 1 text part! > 83 | 1 text part! > 84 | 1 text part! > 85 | 1 text part! > 86 | 1 text part! > 87 | 1 text part! > 88 | 1 text part! > 89 | 1 text part! > 90 | 1 text part! > 91 | 1 text part! > 92 | 1 text part! > 93 | 1 text part! > 94 | 1 text part! > 95 | 1 text part! > 96 | 1 text part! > 97 | 1 text part! > 98 | 1 text part! > 99 | 1 text part! > 100 | 1 text part! > 1 | 2 text part! > 2 | 2 text part! > 3 | 2 text part! > 4 | 2 text part! > 5 | 2 text part! > 6 | 2 text part! > 7 | 2 text part! > 8 | 2 text part! > 9 | 2 text part! > 10 | 2 text part! > 11 | 2 text part! > 12 | 2 text part! > 13 | 2 text part! > 14 | 2 text part! > 15 | 2 text part! > 16 | 2 text part! > 17 | 2 text part! > 18 | 2 text part! > 19 | 2 text part! > 20 | 2 text part! > 21 | 2 text part! > 22 | 2 text part! > 23 | 2 text part! > 24 | 2 text part! > 25 | 2 text part! > 26 | 2 text part! > 27 | 2 text part! > 28 | 2 text part! > 29 | 2 text part! > 30 | 2 text part! > 31 | 2 text part! > 32 | 2 text part! > 33 | 2 text part! > 34 | 2 text part! > 35 | 2 text part! > 36 | 2 text part! > 37 | 2 text part! > 38 | 2 text part! > 39 | 2 text part! > 40 | 2 text part! > 41 | 2 text part! > 42 | 2 text part! > 43 | 2 text part! > 44 | 2 text part! > 45 | 2 text part! > 46 | 2 text part! > 47 | 2 text part! > 48 | 2 text part! > 49 | 2 text part! > 50 | 2 text part! > 51 | 2 text part! > 52 | 2 text part! > 53 | 2 text part! > 54 | 2 text part! > 55 | 2 text part! > 56 | 2 text part! > 57 | 2 text part! > 58 | 2 text part! > 59 | 2 text part! > 60 | 2 text part! > 61 | 2 text part! > 62 | 2 text part! > 63 | 2 text part! > 64 | 2 text part! > 65 | 2 text part! > 66 | 2 text part! > 67 | 2 text part! > 68 | 2 text part! > 69 | 2 text part! > 70 | 2 text part! > 71 | 2 text part! > 72 | 2 text part! > 73 | 2 text part! > 74 | 2 text part! > 75 | 2 text part! > 76 | 2 text part! > 77 | 2 text part! > 78 | 2 text part! > 79 | 2 text part! > 80 | 2 text part! > 81 | 2 text part! > 82 | 2 text part! > 83 | 2 text part! > 84 | 2 text part! > 85 | 2 text part! > 86 | 2 text part! > 87 | 2 text part! > 88 | 2 text part! > 89 | 2 text part! > 90 | 2 text part! > 91 | 2 text part! > 92 | 2 text part! > 93 | 2 text part! > 94 | 2 text part! > 95 | 2 text part! > 96 | 2 text part! > 97 | 2 text part! > 98 | 2 text part! > 99 | 2 text part! > 100 | 2 text part! > 1 | 3 text part! > 2 | 3 text part! > 3 | 3 text part! > 4 | 3 text part! > 5 | 3 text part! > 6 | 3 text part! > 7 | 3 text part! > 8 | 3 text part! > 9 | 3 text part! > 10 | 3 text part! > 11 | 3 text part! > 12 | 3 text part! > 13 | 3 text part! > 14 | 3 text part! > 15 | 3 text part! > 16 | 3 text part! > 17 | 3 text part! > 18 | 3 text part! > 19 | 3 text part! > 20 | 3 text part! > 21 | 3 text part! > 22 | 3 text part! > 23 | 3 text part! > 24 | 3 text part! > 25 | 3 text part! > 26 | 3 text part! > 27 | 3 text part! > 28 | 3 text part! > 29 | 3 text part! > 30 | 3 text part! > 31 | 3 text part! > 32 | 3 text part! > 33 | 3 text part! > 34 | 3 text part! > 35 | 3 text part! > 36 | 3 text part! > 37 | 3 text part! > 38 | 3 text part! > 39 | 3 text part! > 40 | 3 text part! > 41 | 3 text part! > 42 | 3 text part! > 43 | 3 text part! > 44 | 3 text part! > 45 | 3 text part! > 46 | 3 text part! > 47 | 3 text part! > 48 | 3 text part! > 49 | 3 text part! > 50 | 3 text part! > 51 | 3 text part! > 52 | 3 text part! > 53 | 3 text part! > 54 | 3 text part! > 55 | 3 text part! > 56 | 3 text part! > 57 | 3 text part! > 58 | 3 text part! > 59 | 3 text part! > 60 | 3 text part! > 61 | 3 text part! > 62 | 3 text part! > 63 | 3 text part! > 64 | 3 text part! > 65 | 3 text part! > 66 | 3 text part! > 67 | 3 text part! > 68 | 3 text part! > 69 | 3 text part! > 70 | 3 text part! > 71 | 3 text part! > 72 | 3 text part! > 73 | 3 text part! > 74 | 3 text part! > 75 | 3 text part! > 76 | 3 text part! > 77 | 3 text part! > 78 | 3 text part! > 79 | 3 text part! > 80 | 3 text part! > 81 | 3 text part! > 82 | 3 text part! > 83 | 3 text part! > 84 | 3 text part! > 85 | 3 text part! > 86 | 3 text part! > 87 | 3 text part! > 88 | 3 text part! > 89 | 3 text part! > 90 | 3 text part! > 91 | 3 text part! > 92 | 3 text part! > 93 | 3 text part! > 94 | 3 text part! > 95 | 3 text part! > 96 | 3 text part! > 97 | 3 text part! > 98 | 3 text part! > 99 | 3 text part! > 100 | 3 text part! > 1 | 4 text part! > 2 | 4 text part! > 3 | 4 text part! > 4 | 4 text part! > 5 | 4 text part! > 6 | 4 text part! > 7 | 4 text part! > 8 | 4 text part! > 9 | 4 text part! > 10 | 4 text part! > 11 | 4 text part! > 12 | 4 text part! > 13 | 4 text part! > 14 | 4 text part! > 15 | 4 text part! > 16 | 4 text part! > 17 | 4 text part! > 18 | 4 text part! > 19 | 4 text part! > 20 | 4 text part! > 21 | 4 text part! > 22 | 4 text part! > 23 | 4 text part! > 24 | 4 text part! > 25 | 4 text part! > 26 | 4 text part! > 27 | 4 text part! > 28 | 4 text part! > 29 | 4 text part! > 30 | 4 text part! > 31 | 4 text part! > 32 | 4 text part! > 33 | 4 text part! > 34 | 4 text part! > 35 | 4 text part! > 36 | 4 text part! > 37 | 4 text part! > 38 | 4 text part! > 39 | 4 text part! > 40 | 4 text part! > 41 | 4 text part! > 42 | 4 text part! > 43 | 4 text part! > 44 | 4 text part! > 45 | 4 text part! > 46 | 4 text part! > 47 | 4 text part! > 48 | 4 text part! > 49 | 4 text part! > 50 | 4 text part! > 51 | 4 text part! > 52 | 4 text part! > 53 | 4 text part! > 54 | 4 text part! > 55 | 4 text part! > 56 | 4 text part! > 57 | 4 text part! > 58 | 4 text part! > 59 | 4 text part! > 60 | 4 text part! > 61 | 4 text part! > 62 | 4 text part! > 63 | 4 text part! > 64 | 4 text part! > 65 | 4 text part! > 66 | 4 text part! > 67 | 4 text part! > 68 | 4 text part! > 69 | 4 text part! > 70 | 4 text part! > 71 | 4 text part! > 72 | 4 text part! > 73 | 4 text part! > 74 | 4 text part! > 75 | 4 text part! > 76 | 4 text part! > 77 | 4 text part! > 78 | 4 text part! > 79 | 4 text part! > 80 | 4 text part! > 81 | 4 text part! > 82 | 4 text part! > 83 | 4 text part! > 84 | 4 text part! > 85 | 4 text part! > 86 | 4 text part! > 87 | 4 text part! > 88 | 4 text part! > 89 | 4 text part! > 90 | 4 text part! > 91 | 4 text part! > 92 | 4 text part! > 93 | 4 text part! > 94 | 4 text part! > 95 | 4 text part! > 96 | 4 text part! > 97 | 4 text part! > 98 | 4 text part! > 99 | 4 text part! > 100 | 4 text part! > 1 | 5 text part! > 2 | 5 text part! > 3 | 5 text part! > 4 | 5 text part! > 5 | 5 text part! > 6 | 5 text part! > 7 | 5 text part! > 8 | 5 text part! > 9 | 5 text part! > 10 | 5 text part! > 11 | 5 text part! > 12 | 5 text part! > 13 | 5 text part! > 14 | 5 text part! > 15 | 5 text part! > 16 | 5 text part! > 17 | 5 text part! > 18 | 5 text part! > 19 | 5 text part! > 20 | 5 text part! > 21 | 5 text part! > 22 | 5 text part! > 23 | 5 text part! > 24 | 5 text part! > 25 | 5 text part! > 26 | 5 text part! > 27 | 5 text part! > 28 | 5 text part! > 29 | 5 text part! > 30 | 5 text part! > 31 | 5 text part! > 32 | 5 text part! > 33 | 5 text part! > 34 | 5 text part! > 35 | 5 text part! > 36 | 5 text part! > 37 | 5 text part! > 38 | 5 text part! > 39 | 5 text part! > 40 | 5 text part! > 41 | 5 text part! > 42 | 5 text part! > 43 | 5 text part! > 44 | 5 text part! > 45 | 5 text part! > 46 | 5 text part! > 47 | 5 text part! > 48 | 5 text part! > 49 | 5 text part! > 50 | 5 text part! > 51 | 5 text part! > 52 | 5 text part! > 53 | 5 text part! > 54 | 5 text part! > 55 | 5 text part! > 56 | 5 text part! > 57 | 5 text part! > 58 | 5 text part! > 59 | 5 text part! > 60 | 5 text part! > 61 | 5 text part! > 62 | 5 text part! > 63 | 5 text part! > 64 | 5 text part! > 65 | 5 text part! > 66 | 5 text part! > 67 | 5 text part! > 68 | 5 text part! > 69 | 5 text part! > 70 | 5 text part! > 71 | 5 text part! > 72 | 5 text part! > 73 | 5 text part! > 74 | 5 text part! > 75 | 5 text part! > 76 | 5 text part! > 77 | 5 text part! > 78 | 5 text part! > 79 | 5 text part! > 80 | 5 text part! > 81 | 5 text part! > 82 | 5 text part! > 83 | 5 text part! > 84 | 5 text part! > 85 | 5 text part! > 86 | 5 text part! > 87 | 5 text part! > 88 | 5 text part! > 89 | 5 text part! > 90 | 5 text part! > 91 | 5 text part! > 92 | 5 text part! > 93 | 5 text part! > 94 | 5 text part! > 95 | 5 text part! > 96 | 5 text part! > 97 | 5 text part! > 98 | 5 text part! > 99 | 5 text part! > 100 | 5 text part! > </pre> <!-- --> > 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; > > <pre> > x | y | b | t > -: | :--------- | :- | :----------- > 23 | 23 PK text | 23 | 1 text part! > 23 | 23 PK text | 23 | 2 text part! > 23 | 23 PK text | 23 | 3 text part! > 23 | 23 PK text | 23 | 4 text part! > 23 | 23 PK text | 23 | 5 text part! > </pre> <!-- --> > 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; > > <pre> > | QUERY PLAN | > | :--------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1) | > | -&gt; Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) | > | Index Cond: (x = 23) | > | -&gt; Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1) | > | Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) | > | Rows Removed by Filter: 495 | > | Planning Time: 0.094 ms | > | Execution Time: 0.203 ms | > </pre> <!-- --> > 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; > > <pre> > | QUERY PLAN | > | :--------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1) | > | -&gt; Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) | > | Index Cond: (x = 23) | > | -&gt; Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1) | > | Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) | > | Rows Removed by Filter: 495 | > | Planning Time: 0.095 ms | > | Execution Time: 0.176 ms | > </pre> <!-- --> > 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; > > <pre> > | QUERY PLAN | > | :--------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1) | > | -&gt; Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) | > | Index Cond: (x = 23) | > | -&gt; Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1) | > | Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) | > | Rows Removed by Filter: 495 | > | Planning Time: 0.089 ms | > | Execution Time: 0.242 ms | > </pre> <!-- --> > -- > -- Why so many brackets? > -- > > CREATE INDEX ch_b_replace_ix_bis ON child (((REPLACE(b, 'Ref:', ''))::INT)); > > <pre> > ✓ > </pre> <!-- --> > -- > -- Fails! > -- > -- CREATE INDEX ch_b_replace_ix_ter ON child ((REPLACE(b, 'Ref:', ''))::INT) > > <pre> > ✓ > </pre> <!-- --> > -- > -- Works! > -- > -- CREATE INDEX ch_b_replace_ix ON child (CAST(REPLACE(b, 'Ref:', '') AS INT)); > > <pre> > ✓ > </pre> <!-- --> > 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; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=4.44..15.62 rows=2 width=100) (actual rows=5 loops=1) | > | -&gt; Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) | > | Index Cond: (x = 23) | > | -&gt; Bitmap Heap Scan on child ch (cost=4.29..7.43 rows=2 width=64) (actual rows=5 loops=1) | > | Recheck Cond: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) | > | Heap Blocks: exact=3 | > | -&gt; Bitmap Index Scan on ch_b_replace_ix_bis (cost=0.00..4.29 rows=2 width=0) (actual rows=5 loops=1) | > | Index Cond: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) | > | Planning Time: 0.202 ms | > | Execution Time: 0.133 ms | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=5190ca2601ec45c3e744c31dbf5154ba)*
back to fiddle