Thread: Join Table
Hello, I have created the following join table: the two FKs are the PK of the table. Typically, I will need to select rows for a given ITEM_FK. Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? CREATE TABLE SUPPLY ( ITEM_FK integer NOT NULL, CONTACT_FK integer NOT NULL, COST numeric (7,2), PRIMARY KEY (ITEM_FK,CONTACT_FK) ); -- Regards/Gruß, Tarlika Elisabeth Schmitz
On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote: > > I have created the following join table: the two FKs are the PK of the > table. Typically, I will need to select rows for a given ITEM_FK. > > Question: is it necessary/advisable to create an index for the ITEM_FK > column? Or is this redundantbecause this column is already one of the PK > columns? Here's an excerpt from the documentation for CREATE TABLE: "PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce the uniqueness. Thus, it is not necessary to create an explicit index for primary key columns." However, read the "Multicolumn Indexes" section in the "Indexes" chapter to be sure you understand when the index will be used and when it won't be: http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html > CREATE TABLE SUPPLY > ( > ITEM_FK integer NOT NULL, > CONTACT_FK integer NOT NULL, > COST numeric (7,2), > PRIMARY KEY (ITEM_FK,CONTACT_FK) > ); If ITEM_FK and CONTACT_FK are foreign keys, then you might want to add foreign key constraints to ensure referential integrity. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
T E Schmitz <mailreg@numerixtechnology.de> writes:
> CREATE TABLE SUPPLY
> (
> ITEM_FK integer NOT NULL,
> CONTACT_FK integer NOT NULL,
> COST numeric (7,2),
> PRIMARY KEY (ITEM_FK,CONTACT_FK)
> );
> Question: is it necessary/advisable to create an index for the ITEM_FK
> column? Or is this redundantbecause this column is already one of the PK
> columns?
The PK index will be usable for searches on ITEM_FK alone (though *not*
for searches on CONTACT_FK alone --- a moment's thought about the index
sort order should convince you why). It would be marginally less
efficient for this purpose than a dedicated index on ITEM_FK. But unless
your use of this table is almost all searches and hardly any
modifications, adding a dedicated index is probably a net loss due to
the added update costs.
regards, tom lane
T E Schmitz wrote: > Hello, > > I have created the following join table: the two FKs are the PK of the > table. Typically, I will need to select rows for a given ITEM_FK. > > Question: is it necessary/advisable to create an index for the ITEM_FK > column? Or is this redundantbecause this column is already one of the PK > columns? > > CREATE TABLE SUPPLY > ( > ITEM_FK integer NOT NULL, > CONTACT_FK integer NOT NULL, > COST numeric (7,2), > PRIMARY KEY (ITEM_FK,CONTACT_FK) > ); The primary-key index can be used for ITEM_FK but not CONTACT_FK, so you might want an index on that column. -- Richard Huxton Archonet Ltd
Hello Mike/Tom/Richard, Thank you for your replies. Michael Fuhr wrote: > On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote: > >>Question: is it necessary/advisable to create an index for the ITEM_FK >>column? Or is this redundantbecause this column is already one of the PK >>columns? > > > However, read the "Multicolumn Indexes" section in the "Indexes" > chapter to be sure you understand when the index will be used and > when it won't be: > > http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html I see. If using a multi-column PK, the order matters. So, if I want to access the table both via the 1st and 2nd PK column, I would have to define an index for the 2nd column to avoid a full table scan. Let's ask the question the other way round: I remember seeing a discussion (re Oracle) whether to use a multi-column PK or a unique constraint in such a situation - I got the impression it is one of these "religious" discussions ;-). What are the pros and cons? I have a few join tables. In the example I gave earlier the rows might be updated and selected via either of the two FKs. I have other join tables the rows of which will never be updated. In any case, the FK/FK combination has to be unique. If using a multi-column unique constraint, I presume the order matters just as it does with multi-column PKs? > If ITEM_FK and CONTACT_FK are foreign keys, then you might want to > add foreign key constraints to ensure referential integrity. Thank you for the tip. I had done that further down in my init file via an ALTER TABLE...ADD CONSTRAINT. -- Regards/Gruß, Tarlika Elisabeth Schmitz
On Mon, Nov 01, 2004 at 06:12:02PM +0000, T E Schmitz wrote: > I see. If using a multi-column PK, the order matters. > So, if I want to access the table both via the 1st and 2nd PK column, I > would have to define an index for the 2nd column to avoid a full table scan. If you want to use an index scan when querying by the 2nd column alone then you'd need to create an index on it. Queries using the 1st column alone or the 1st column with the 2nd column will use the primary key index. You can use EXPLAIN ANALYZE to see which index, if any, the planner uses. > Let's ask the question the other way round: I remember seeing a > discussion (re Oracle) whether to use a multi-column PK or a unique > constraint in such a situation - I got the impression it is one of these > "religious" discussions ;-). > What are the pros and cons? Here's an excerpt from PostgreSQL's CREATE TABLE documentation: Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary keyalso provides metadata about the design of the schema, as a primary key implies that other tables may rely on this setof columns as a unique identifier for rows. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
T E Schmitz wrote: >> On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote: >> >>> Question: is it necessary/advisable to create an index for the >>> ITEM_FK column? Or is this redundantbecause this column is already >>> one of the PK columns? >> >> However, read the "Multicolumn Indexes" section in the "Indexes" >> chapter to be sure you understand when the index will be used and >> when it won't be: >> >> http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html > > I see. If using a multi-column PK, the order matters. > So, if I want to access the table both via the 1st and 2nd PK column, I > would have to define an index for the 2nd column to avoid a full table > scan. > > Let's ask the question the other way round: I remember seeing a > discussion (re Oracle) whether to use a multi-column PK or a unique > constraint in such a situation - I got the impression it is one of these > "religious" discussions ;-). > What are the pros and cons? Oracle uses a tactic called 'index skip scan' that tries to make use of an index when the first column is not restricted by the query. http://www.oracle-base.com/articles/9i/IndexSkipScanning.php The idea is that scanning the data in the index is fast, and the results sets of rowids can be sorted and batched for (relatively) efficient retrieval from the heap. I've read one review that indicates there were pitfalls with using it in 9i: http://www.praetoriate.com/oracle_tips_skip_scan.htm Having used such schemes for querying (blush) ISAM files I'd say that this isn't surprising.