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

Multicolumn Primary Key

From: Thomas F(dot)O'Connell <tfo(at)sitening(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Multicolumn Primary Key
Date: 2004-08-31 17:00:11
Message-ID: 38891E66-FB6F-11D8-A844-000D93AE0944@sitening.com (view raw or flat)
Thread:
Lists: pgsql-general
We've got a table that has a definition as follows:

CREATE TABLE linking_table (
	fk		int8 REFERENCES source_table( pk1 ),
	value	int8,
	PRIMARY KEY( fk1, value )
);

I would've thought that the multicolumn primary key would behave as a 
multicolumn index is supposed to behave per

http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html

where the behavior of the index cascades from the left rightward across 
any columns specified in WHERE.

But a query like

SELECT COUNT( * ) FROM linking_table WHERE fk = '42';

yields a sequential scan.

If I add an index to fk, then the same query yields an index scan, as I 
would expect. Is this because, according to the docs, a primary key "is 
merely a combination of UNIQUE and NOT NULL"?

If so, then why do primary keys afford index scans of single columns 
specified as primary keys?

This is in postgres 7.4.5, btw.

-tfo

Responses

pgsql-general by date

Next:From: Daniel SchuchardtDate: 2004-08-31 17:06:33
Subject: Re: Microsoft Project and PostgreSQL?
Previous:From: John Sidney-WoollettDate: 2004-08-31 16:23:23
Subject: Re: Hebrew support -- please help !

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