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.
/Fredric
...
IF (SELECT EXISTS (SELECT 1 FROM t1 WHERE
volume_id = NEW.volume_id AND name = NEW.name)) THEN
RAISE EXCEPTION '% already exists on volume', NEW.name;
END IF;
...
This trigger is called only BEFORE INSERTS.
In testing, without the trigger, inserts are at 10,000 every 2 seconds.
With the trigger, they are 10,000 every 40 seconds. The output of
explain analyze suggests that this shouldn't be taking so long.
EXPLAIN ANALYZE SELECT EXISTS (
SELECT 1 FROM t1 WHERE volume_id = 300 AND name = 'whodat');
Result (cost=8.35..8.36 rows=1 width=0) (actual time=0.019..0.020
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Index Scan using volume_id_idx on t1 (cost=0.00..8.35 rows=1
width=0) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (volume_id = 300)
Filter: (name = 'whodat'::text)
Total runtime: 0.053 ms
(6 rows)
0.053 ms/record / 1,000 ms/sec * 10,000 records = .53 seconds
According to that stat, this lookup should be adding about 0.5 seconds
to 10,000 records, far from the actual 38 seconds it is adding. I've
tried to change up the query in the trigger to see if I could get
different results with not much luck. Any idea what might be taking up
the extra time or what I can do to troubleshoot?
Thanks.
-Randall
begin:vcard
fn:Fredric Fredricson
n:Fredricson;Fredric
org:Ln4 Solutions AB
email;internet:Fredric.Fredricson@xxxxxxxxxxxxx
title:CTO
tel;home:+46 8 91 64 39
tel;cell:+46 70 677 58 48
version:2.1
end:vcard
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
