Re: Batch update of indexes

On 2/4/16 1:37 AM, konstantin knizhnik wrote:
>> >My suspicion is that it would be useful to pre-order the new data before trying to apply it to the indexes.
> Sorry, but ALTER INDEX is expected to work for all indexes, not only B-Tree, and for them sorting may not be possible...
> But for B-Tree presorting inserted data should certainly increase performance.
> I will think about it.

I wasn't talking about ALTER INDEX.

My theory is that if you're doing a large DML operation it might be more
efficient to update an index as a single bulk operation, instead of
doing it for each tuple.

If you want to do that, then you need an efficient method for finding
everything that a DML statement changed. That's the exact same thing we
need to support statement-level triggers being able to reference NEW and
OLD. It's probably also what we need to support incremental update matviews.

If we had such a capability then we could add options to the AM
infrastructure to allow indexes to support doing bulk maintenance as
well as per-tuple maintenance (or even support only bulk maintenance...)

I don't think any of that has anything to do with ALTER INDEX.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com