| 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 |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Schuchardt | 2004-08-31 17:06:33 | Re: Microsoft Project and PostgreSQL? |
| Previous Message | John Sidney-Woollett | 2004-08-31 16:23:23 | Re: Hebrew support -- please help ! |