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 unique index 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 matters to 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 in the b-tree, maybe? Thanks in advance for any advice. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
