Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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.



pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group