Re: Trigger Performance

On 2011-01-17, Fredric Fredricson <Fredric(dot)Fredricson(at)bonetmail(dot)com> 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