Search Postgresql Archives

Re: Index on a Decrypt / Bytea2Text Function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux