Re: Index on a Decrypt / Bytea2Text Function

On Wed, 2010-07-14 at 20:32 +0100, Thom Brown wrote:
> On 14 July 2010 20:23, Anthony Presley <anthony(at)resolution(dot)com> wrote:
> > 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
> >
> >
>
> Would the following work?:
>
> CREATE INDEX idx_employee_functional ON employee
> (bytea2text(DECRYPT(DECODE(ssn,'hex'), text2bytea(CAST(id AS
> text)),'bf'))
>
> Thom

Unfortunately, that doesn't work:

# CREATE INDEX idx_employee_functional ON employee
(bytea2text(DECRYPT(DECODE(ssn,'hex'), text2bytea(CAST(id AS
text)),'bf')));
ERROR: functions in index expression must be marked IMMUTABLE

Guess we'll need to come up with something else.

--
Anthony