speedup ALTER TABLE ADD CHECK CONSTRAINT.
hi.
attached patch trying to speedup ALTER TABLE ADD CHECK CONSTRAINT.
demo:
drop table if exists t7;
create table t7 (a int not null, b int, c int);
insert into t7 select g, g+1, g %100 from generate_series(1,1_000_000) g;
create index on t7(a,b);
alter table t7 add check (a > 0);
patch: Time: 4.281 ms
master: Time: 491.689 ms
----------------------------
implementation:
step1. during execute command `ALTER TABLE ADD CHECK CONSTRAINT`
in AddRelationNewConstraints->StoreRelCheck
after StoreRelCheck add a CommandCounterIncrement();
so previously added CHECK pg_constraint can be seen by us.
we need to use pg_get_constraintdef to retrieve the CHECK constraint definition.
step2. check if this relation has any suitable index (not expression
index, not predicate index)
--whole patch hinges on SPI query can use indexscan to quickly
retrieve certain information.
step3: construct and execute these three SPI query:
("set enable_seqscan to off")
(SELECT 1 FROM the_table WHERE NOT (check_constraint_def)
AND check_constraint_def IS NOT NULL LIMIT 1)
("reset enable_seqscan")
the SPI query will be faster, if the qual(check_constraint_def) can be
utilized by indexscan.
if SPI_processed == 0 means we toggle this check constraint as
"already_validated" (bool) is true.
we stored already_validated in CookedConstraint. later pass it to
AlteredTableInfo->constraints.
then phrase 3 within ATRewriteTable, we can do
```
if (constraint->already_validated)
needscan = false;
```
----------------------------
concern:
only certain kinds of check constraint expressions can be used for
this optimization.
i do all the CHECK constraint expressions filter in checkconstraint_expr_walker.
use contain_volatile_functions to filter out volatile expressions,
add (check_constraint_def IS NOT NULL) in the above SPI query, i think
null handling is correct?
ALTER TABLE ADD CHECK CONSTRAINT is using ACCESS EXCLUSIVE lock.
but i need to think more about concurrently related issues.
idea come from this thread:
/messages/by-id/CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com
Attachments:
v1-0001-speedup-alter-table-add-check-constraint.patchtext/x-patch; charset=US-ASCII; name=v1-0001-speedup-alter-table-add-check-constraint.patchDownload+345-2
Hello!
Thanks for the patch, but I think using SPI is still not allowed here: /messages/by-id/9878.1511970441@sss.pgh.pa.us
if it uses SPI for sub-operations of ALTER TABLE I think that is sufficient reason to reject it out of hand.
regards, Sergei
Import Notes
Resolved by subject fallback
Sergei Kornilov <sk@zsrv.org> writes:
Hello!
Thanks for the patch, but I think using SPI is still not allowed here: /messages/by-id/9878.1511970441@sss.pgh.pa.us
Yeah, if you want to do this you need to code the catalog lookup in
C. SPI just adds too many variables to what will happen, on top
of being enormously expensive compared to a handwritten lookup.
regards, tom lane
On Mon, Dec 2, 2024 at 12:06 AM Sergei Kornilov <sk@zsrv.org> wrote:
Hello!
Thanks for the patch, but I think using SPI is still not allowed here: /messages/by-id/9878.1511970441@sss.pgh.pa.usif it uses SPI for sub-operations of ALTER TABLE I think that is sufficient reason to reject it out of hand.
Thanks for dropping this link.
i wonder what was your thoughts about this
(/messages/by-id/900056D1-32DF-4927-8251-3E0C0DC407FD@anarazel.de)
at that time?