speedup ALTER TABLE ADD CHECK CONSTRAINT.

Started by jian heover 1 year ago4 messageshackers
Jump to latest
#1jian he
jian.universality@gmail.com

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
In reply to: jian he (#1)
Re: speedup ALTER TABLE ADD CHECK CONSTRAINT.

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sergei Kornilov (#2)
Re: speedup ALTER TABLE ADD CHECK CONSTRAINT.

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

#4jian he
jian.universality@gmail.com
In reply to: Sergei Kornilov (#2)
Re: speedup ALTER TABLE ADD CHECK CONSTRAINT.

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.us

if 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?