Re: [GENERAL] 7.4Beta

On Fri, 15 Aug 2003, Andreas Pflug wrote:

> Stephan Szabo wrote:
>
> >On Fri, 15 Aug 2003, Andreas Pflug wrote:
> >
> >
> >
> >>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.
> >
> >
> This really depends. If a constraint is just a check on the
> 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.