Thread: Are indices used for creating check constraints?
Here are 3 interesting (to me) numbers:
1. creating index on expression (func(timestamp)) takes 5 seconds
2. creating check constraint on the same expression takes 10 seconds
3. adding partition table based on the same expression without check expression takes 20 seconds (this has been resolved by answer from David Rowley)
I tried various kind of indices to optimize 2. but nothing helps. When creating check constraint on column values directly is very fast (seems analyze helps), but creating check constraint on expression is very slow, even twice as long as creating index on the same expression.
Is it possible to use indices to create check expressions?
Why is 2. twice as long as 1.?
Why is 3. twice as long as 2.? (when there is no appropriate check constraint for 3.)
Thank you,
Dinko
Sent from Outlook
Dinko Papak <rimokatolik@outlook.com> writes:
> Here are 3 interesting (to me) numbers:
> 1. creating index on expression (func(timestamp)) takes 5 seconds
> 2. creating check constraint on the same expression takes 10 seconds
> 3. adding partition table based on the same expression without check expression takes 20 seconds (this has been
resolvedby answer from David Rowley)
Hmm ...
> Is it possible to use indices to create check expressions?
No.
> Why is 2. twice as long as 1.?
Good question. Could you provide a concrete test case?
regards, tom lane