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

Re: What order in primary key definition ?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: franck(dot)routier(at)axege(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: What order in primary key definition ?
Date: 2006-07-27 12:39:35
Message-ID: 5574.1154003975@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
franck(dot)routier(at)axege(dot)com writes:
> I am wondering if the order in which primary key fields are ordered has an
> impact on performance...

Putting the more specific key first would make for a small improvement
in the performance of the index, because key comparisons would more
often be able to make a decision after comparing the first column and
not have to compare the second.  This is not a big deal though.

What I'd counsel asking yourself is whether you're ever going to search
the table with only one of the columns specified, and if so put that one
first.  This will save needing a second index.  If you need to search
by *both* columns independently, then you will need two indexes anyway,
one on (a,b) and one on (b) --- see
http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2006-07-27 12:56:34
Subject: Re: pg_dump ordering in 8.1.3
Previous:From: Sue FittDate: 2006-07-27 09:54:10
Subject: pg_dump ordering in 8.1.3

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