Index on a Decrypt / Bytea2Text Function

Hi all,

We tend to do a lot of lookups on our database that look something like:

select
    e.id
from
employee e ,app_user au
    where
au.id=user_id and
au.corporation_id=$1 and
e.ssn is not null and
e.ssn!=' ' and
e.ssn!='' and
e.deleted='N'and
bytea2text(DECRYPT(decode(e.ssn,'hex'), text2bytea(cast(e.id as text)),
'bf'))=$2

The analyze here looks like:

> explain analyze select e.id from employee e ,app_user au where
au.id=user_id and au.corporation_id=41197 and  e.ssn is not null and
e.ssn!=' ' and e.ssn!='' and e.deleted='N'and
bytea2text(DECRYPT(decode(e.ssn,'hex'), text2bytea(cast(e.id as text)),
'bf'))='188622250';

QUERY
PLAN
--------------------------------------------------------------------------
 Nested Loop  (cost=0.00..19282.05 rows=122 width=8) (actual
time=24.591..192.435 rows=1 loops=1)
   ->  Index Scan using emp_del on employee e  (cost=0.00..18625.99
rows=122 width=16) (actual time=24.556..192.398 rows=1 loops=1)
         Index Cond: (deleted = 'N'::bpchar)
         Filter: ((ssn IS NOT NULL) AND (ssn <> ' '::text) AND (ssn <>
''::text) AND (bytea2text(decrypt(decode(ssn, 'hex'::text),
text2bytea((id)::text), 'bf'::text)) = '188622250'::text))
   ->  Index Scan using app_user_pkey on app_user au  (cost=0.00..5.36
rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
         Index Cond: (au.id = e.user_id)
         Filter: (au.corporation_id = 41197)
 Total runtime: 192.565 ms
(8 rows)

It would appear that almost 100% of this time is taken up by doing the
bytea2text and decrypt() functions.

How would I create an index based on the results of the decrypt and
bytea2text function to improve this select statement?

Thanks!


--
Anthony