pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes it matters the column order in indexes and constraints creation?
2+ messages / 2 participants
[nested] [flat]
* Does it matters the column order in indexes and constraints creation?
@ 2007-01-11 10:56 Arnau <arnaulist@andromeiberica.com>
0 siblings, 1 reply; 2+ messages in thread
From: Arnau @ 2007-01-11 10:56 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I've got a doubt about how to create an index and a primary key. Lets
say I have the following table:
CREATE TABLE blacklist
(
telephone VARCHAR(15),
customer_id INT4
CONSTRAINT fk_blacklist_customerid REFERENCES
customers( customer_id ),
country_id INT2
CONSTRAINT fk_blacklist_countryid REFERENCES
countries( country_id ),
CONSTRAINT pk_blacklist_cidcustidtel
PRIMARY KEY(country_id, customer_id, telephone)
);
The country_id column can have maybe 100 - 250 different values.
The customer_id column can have as much several hundred values (less
than 1000).
The telephone is where all will be different.
So my doubt is, in terms of performance makes any difference the
order of the primary key fields? The same in the index definition? I
have checked the postgresql documentation I haven't been able to find
anything about.
Thanks
--
Arnau
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Does it matters the column order in indexes and constraints
@ 2007-01-11 11:27 Richard Huxton <dev@archonet.com>
parent: Arnau <arnaulist@andromeiberica.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2007-01-11 11:27 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau wrote:
> Hi all,
>
> I've got a doubt about how to create an index and a primary key. Lets
> say I have the following table:
> The country_id column can have maybe 100 - 250 different values.
> The customer_id column can have as much several hundred values (less
> than 1000).
> The telephone is where all will be different.
>
> So my doubt is, in terms of performance makes any difference the order
> of the primary key fields? The same in the index definition? I have
> checked the postgresql documentation I haven't been able to find
> anything about.
Well, it makes no *logical* difference, but clearly the index will have
a different shape depending on how you create it.
If you regularly write queries that select by country but not by
customer, then use (country_id,customer_id). A more likely scenario is
that you will access via customer, which in any case is more selective.
However, since both colums reference other tables you might want to make
sure the "secondary" column has its own index if you do lots of updating
on the target FK table.
But, the more indexes you have the slower updates will be on this table,
since you'll need to keep the indexes up-to-date too.
I find it's easier to spot where to put indexes during testing. It's
easy to add indexes where they're never used.
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-01-11 11:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-11 10:56 Does it matters the column order in indexes and constraints creation? Arnau <arnaulist@andromeiberica.com>
2007-01-11 11:27 ` Re: Does it matters the column order in indexes and constraints Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox