On 14 July 2010 20:32, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > In response to Anthony Presley <anthony@xxxxxxxxxxxxxx>: > >> 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? > > The best way would be to unencrypt the column and use a normal index. > > Since you're simply using a value in another column as the key anyway, > your design has created all the performance headaches of encryption > with no actual security. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- Yes, I immediately thought about what's actually happening as soon as I sent the last message. Forget the functional index. Thom -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
