On 2011-01-17, Fredric Fredricson <Fredric.Fredricson@xxxxxxxxxxxxx> wrote: > This is a multi-part message in MIME format. > --------------000600030501040304020000 > Content-Type: text/plain; charset=UTF-8; format=flowed > Content-Transfer-Encoding: 7bit > > On 01/15/2011 11:52 PM, Randall Smith wrote: >> Hi, >> >> I've created a trigger that checks the uniqueness of two columns in a >> table. Traditionally, one would use a unique constraint, but in my >> case, the size of the unique index would be too large and some >> performance loss is acceptable. However, the trigger performance seems >> to be far below what's indicated by an explain analyze of the query used >> in the trigger. >> >> The unique fields consist of a an indexed int8 (volume_id) and a text >> field (name). The average ratio of volume_id to name is 1 to 10,000. >> The query I'm using to check uniqueness in the trigger is: > A possible work around could be to store an extra field that contains > the md5 hash of the text field and use that field in the index instead > of the text itself. The md5 sum could be calculated by your trigger. you don't have to store it, only index on it. create unique index t1_nodups on t1( volume_id,md5(name)); -- ââ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
