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 <x-tad-bigger>UNIQUE</x-tad-bigger> and <x-tad-bigger>NOT NULL"</x-tad-bigger>?
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
