I am relatively new to Postgres but this is my suggestion: Try to add an index on the column substring"(p.SERIAL_NUMBER::TEXT, '[A-Z][0-9]+'::TEXT) ?
Also, analyze the table after adding the index. If index scan is chosen after this performance might improve(I tried to experiment by creating a table with a text field in it and running a similar query on it).