On Fri, 15 Aug 2003, Andreas Pflug wrote:
Stephan Szabo wrote:
On Fri, 15 Aug 2003, Andreas Pflug wrote:This really depends. If a constraint is just a check on the
Stephan Szabo wrote:
That really needs to be rewritten to do a single check over the table rather than running the constraint for every row. I keep meaning to get around to it and never actually do. :( I'm not sure that in practice you'll get a better plan at restore time depending on what the default statistics give you.
This is clearly a case for a statement level trigger, as soon as affected rows can be identified.
Well, I think single inserts might be more expensive (because the query is more involved for the table joining case) using a statement level trigger, so we'd probably want to profile the cases.
inserted/updated column, so no other row needs to be checked, there's no
faster way then the current row trigger. But FK constraints need to
execute a query to retrieve the referenced row, and every RDBMS prefers
to execute a single statement with many rows over many statements with a
single row, because the first will profit from optimization. And even if
only a single row is inserted or updated, there's still the need to
lookup the reference.
I don't think that addresses the issue I brought up. If you're doing a bunch of single inserts: begin; insert into foo values (1); insert into foo values (1); insert into foo values (1); insert into foo values (1); insert into foo values (1); end;
Each of those statement triggers is still only going to be dealing with a single row. If you're in immediate mode there's not much you can do about that since the constraint is checked between inserts. If you're in deferred mode, right now it won't help because it's not going to batch them, it's going to be 5 statement triggers AFAICT each with its own 1 row affected table.
I believe that the more complicated join the old/new table with the pk table and do the constraint check is going to be slightly slower than the current row behavior for such cases because the trigger query is going to be more complicated. What would be nice would be some way to choose whether to use a single query per statement vs a simpler query per row based on what's happening.
Deferring the constraint check would mean checking 5 single rows, right. But I still can't see why you think that a row level trigger would be cheaper in this case. I had a look at ri_triggers.c and what's coded there looks just as I expected, doing a query on the referenced table. the queries might look a bit different when checking multiple rows at once, but carefully designed I doubt that there would be a performance hit from this. In case it *is* significantly slower, single row updates could be handled separately using the current triggers, and statement triggers for multiple rows. This would cover both scenarios best. At the moment, update/insert scales not too good.
Best thing in the situation above would certainly be if all 5 rows would be checked in a single query, but that looks quite impossible because a mixture of inserts/updates/deletes on different tables might be deferred.
Regards, Andreas
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
