I have a partitioned table with a multi-column unique index. The table is partitioned on a timestamp with time zone
column. (I realize this has nothing to do with the unique index.) The original unique index was in the order
(timestamptz,varchar, text, text) and most queries against it were slow. I changed the index order to (varchar, text,
timestamptz,text) and queries now fly, but loading data (via copy from stdin) in the table is 2-4 times slower. The
uniqueindex is required during the load.
The original index is in the same order as the table's columns (2,3,4,5), while the changed index is in column order
(3,5,2,4). I've tested this several times and the effect is repeatable. It does not seem the column order in the table
mattersto the insert/index performance, just the column order in the index.
Why would changing the column order on a unique index cause data loading or index servicing to slow down? Page splits
inthe b-tree, maybe?
Thanks in advance for any advice.