Hi all,
I was playing around with ways to make a schema change recently to a ~30M record table. I wanted to add a new nullable, non-default-valued column to this existing table, and then add a new partial to that table, where the partial index condition refers to a value in that newly added column. I was expecting that there might be an optimisation here that PostgreSQL could make, given the partial index condition could not be hit, but it seems not.
Here's what I was playing with:
tmp=> \timing on
Timing is on.
tmp=> BEGIN;
BEGIN
Time: 1.333 ms
tmp=> ALTER TABLE foo ADD COLUMN d integer NULL;
ALTER TABLE
Time: 1.581 ms
tmp=> CREATE UNIQUE INDEX myindex ON foo (a, b, c) where d = 2;
CREATE INDEX
Time: 37758.880 ms (00:37.759)
tmp=> COMMIT;
COMMIT
Time: 3.922 ms
Given that d = 2 could not ever be true as the nullable, non-default-valued column was just added inside the same transaction, I was hoping that the index creation would be instantaneous, as it realised there's no rows that this condition could be true for.
I definitely don't claim to be a databases expert. Is there something I'm missing as to why this optimisation could not be put in place? If this seems like a reasonable optimisation that could be made, is there a place that I should post / record it for future reference / assessment by the core developers?
Cheers,
Tim