An index in postgresql can be on conversion (encoding/decoding) of a value

I needed to do a join between two tables where the key was basically the same, but stored in different formats. In one it was stored as a hex value in a text field, and in the other in a bytea field.

In one table there would be about 360'000 rows and in the other about 75 million. How can I efficiently do a join when I need to convert one of the values in the join criterion? It turns out that in postgresql you can not only make indexes for the values of a column, but you can also make an index for a certain conversion of the value. So if the sql would contain something like this:

 INNER JOIN table2 ON table1.txid = encode(table2.txid, 'HEX')

I can create an index like so:

 CREATE INDEX ON table2 (encode(txid, 'HEX'))