From: | Thom Brown <thombrown(at)gmail(dot)com> |
---|---|
To: | Anthony Presley <anthony(at)resolution(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index on a Decrypt / Bytea2Text Function |
Date: | 2010-07-14 19:32:44 |
Message-ID: | AANLkTilSkQeZcWU7w8IFS7mnB3YZEPt0o3w79ldu9qvf@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2010-07-14 19:32:46 | Re: Index on a Decrypt / Bytea2Text Function |
Previous Message | Joshua D. Drake | 2010-07-14 19:26:28 | PgWest 2010 Call for Papers! |