Insert performance and multi-column index order

From: bob_lunney(at)yahoo(dot)com
To: pgsql-performance(at)postgresql(dot)org
Subject: Insert performance and multi-column index order
Date: 2009-06-26 17:25:29
Message-ID: 155063.67512.qm@web39705.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dave North 2009-06-26 17:39:48 Re: Nested Loop "Killer" on 8.1
Previous Message Robert Haas 2009-06-26 15:00:24 Re: GiST index performance