Re: Multicolumn Primary Key

[Martijn van Oosterhout <kleptog(at)svana(dot)org>]

How many rows in the table? If it's a small table PostgreSQL won't
consider using the index.

Otherwise, have you VACUUM ANALYZEd recently?

On Tue, Aug 31, 2004 at 12:00:11PM -0500, Thomas F. O'Connell wrote:
> 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

-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment: pgpDVYrzJlBGr.pgp
Description: PGP signature