I would be cautious about having two SQL-statements in the same prepare - it is against the DBI spec.
Some drivers may allow it (it looks as if Postgres does), but they shouldn't.

Note I have no Postgres expierience, so the following is just general RDB expierince

I would look at the way the query optimizer handles the queries; a loop of 3 SQl statements executed 400 times should only take a few seconds - and does only take that amount of time in perl/DBI. The rest of the time is spent in your database, so it is there that you have to look for the optimisations.

You are doing a large number of lookups based on the orf column in yeast1 - is there an index on that column? If not you probably have a table scan for every update - and that will take some time.

Does the profile_index table have a structure that easily allows inserts?

And all in all: you are making several hundred updates to the same row, one for each column. It will be several hundred times as fast to collect all these updates into one gigantic statement (if the DB can handle it). There is no reason to go overboard with placeholders and prepare_cached unless you have millions of prepares, just do it the simple way:
build your statement with the values in place, prepare and execute it.

That should be a couple of hundred times faster.