On Wed, 2010-07-14 at 20:32 +0100, Thom Brown wrote: > On 14 July 2010 20:23, Anthony Presley <anthony@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
