Postgres discrepency in trigger execution speed?

You're setting up the trigger to run for each row, and then inside the trigger function you're doing another select on the whole table. Do one or the other. (Try changing FOR EACH ROW to FOR EACH STATEMENT.).

Tried that, no difference :-( – Mark Oct 14 '10 at 14:39 Hmm. Well, have you looked at the query plan when you OR the two queries together? Can you combine the two queries by moving the logic into a single WHERE clause?

– Pointy Oct 14 '10 at 14:45 Not yet - what I might try is trying the function with one of the queries missing in case it is doing some odd sort of mashing together of the 2 queries. I'll report back.. – Mark Oct 14 '10 at 14:52 OK - I'm not a super database genius, but one of the things I've learned is that "OR" has a way of causing database servers to get stupid. – Pointy Oct 14 '10 at 14:53 I've reduced the function to just do the first query.It takes exactly half the speed, so there's still an issue.

I really don't understand how me running the query manually can amount to being 10's of times faster than running it when triggered :-( – Mark Oct 14 '10 at 14:55.

It looks like postgres may sometimes create a different plan if the query has been prepared for the function. If I change the function to actually execute the SQL then it creates a new plan every time and it does operate much faster for my particular scenario (strangely! ) This basically solves my problem: CREATE OR REPLACE FUNCTION func_fk_location_area() RETURNS "trigger" AS $$ DECLARE myst TEXT; mysv TEXT; myrec RECORD; BEGIN myst := 'SELECT id FROM location WHERE NOT EXISTS (SELECT id FROM area WHERE area.

Key=location. Key AND area. Id=location.

Area_id '; mysv := 'AND ((area. Tr_fromlocation. Tr_from) OR (area.

Tr_from=location. Tr_from AND area. Tr_until=location.

Tr_from)))'; EXECUTE myst || mysv; IF FOUND THEN RAISE EXCEPTION 'FK location_area integrity violation. '; RETURN NULL; END IF; mysv := 'AND ((area. Tr_from=location.

Tr_until) OR (area. Tr_from=location. Tr_until AND area.

Tr_until=location. Tr_until)))'; EXECUTE myst || mysv; IF FOUND THEN RAISE EXCEPTION 'FK location_area integrity violation. '; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_fk_area_location AFTER DELETE OR UPDATE ON area FOR EACH ROW EXECUTE PROCEDURE func_fk_location_area(); CREATE TRIGGER trigger_fk_location_area AFTER INSERT OR UPDATE ON location FOR EACH ROW EXECUTE PROCEDURE func_fk_location_area().

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.