| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Andres Freund <andres(at)anarazel(dot)de> |
| Cc: | Александр Асафов <asafofalexander(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Optimization of partial index creation for a new column |
| Date: | 2025-12-14 22:12:25 |
| Message-ID: | 1822695.1765750345@sss.pgh.pa.us |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2025-12-14 23:13:49 +0300, Александр Асафов wrote:
>> I have a feature suggestion to optimize the creation of a partial
>> index. In some cases, it is possible to skip a full table scan. For
>> example:
>> ...
>> How complex would a patch with this optimization be? Will it be
>> necessary to add any attributes to the table metadata, or can we check
>> that the previous command was CREATE TABLE when creating an index?
> I doubt this is feasible, or at least that it's going to be worth the
> complexity. You'd have to make sure that the transaction didn't actually
> insert any rows, and update some in-memory or catalog state if it did. With
> subtransactions etc that could get somewhat complicated.
Even if it could be made to work, I think it likely would be a net
negative for performance. You're suggesting that we add mechanism to
track whether any insertion/update has happened since an ALTER ADD
COLUMN, which means new overhead for every INSERT and UPDATE. Maybe
not a huge amount of overhead, but probably not trivial. That
overhead would only be repaid in the rather unlikely case where a
suitable index gets added soon after the ALTER. Unless you can find
some substantially-more-common scenarios where the overhead would
produce a benefit, it's hard to believe we'd accept such a patch.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2025-12-14 22:55:36 | Re: POC: make mxidoff 64 bits |
| Previous Message | Tom Lane | 2025-12-14 21:38:59 | Re: Proposed mid-cycle update of typedefs.list |