From: | Tim Dawborn <tim(dot)dawborn(at)gmail(dot)com> |
---|---|
To: | "Psql_General (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Potential optimisation for the creation of a partial index condition over a newly created nullable, non-default-valued column? |
Date: | 2020-06-04 05:46:52 |
Message-ID: | CAN9Kr4AY+-eoOoxR_9bChpCNRPvXziaXozuXYVfBtDBAk0wVrg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-06-04 05:56:49 | Re: Can we get SQL Server-like cross database queries |
Previous Message | Thomas Munro | 2020-06-04 04:41:04 | Re: Can we get SQL Server-like cross database queries |