Multicolumn Primary Key

[Thomas F.O'Connell]

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/s...lticolumn.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]

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/s...lticolumn.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 <kl*****@svana. 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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBNO7JY5T wig3Ge+YRAmuTAK CX/0p5KC5SY+psqlEj a62dARIbGgCgu/Zl
nXz94GwoRnPxoM2 XV5LRL3o=
=tMg/
-----END PGP SIGNATURE-----

[Thomas F.O'Connell]

On Aug 31, 2004, at 4:34 PM, Martijn van Oosterhout wrote:

How many rows in the table? If it's a small table PostgreSQL won't
consider using the index.
300,000+
Otherwise, have you VACUUM ANALYZEd recently?

Argh! That seems to have been it. I have pg_autovacuum running, so it
didn't occur to me.

Thanks.

-tfo
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Matthew T. O'Connor]

Thomas F.O'Connell wrote:

On Aug 31, 2004, at 4:34 PM, Martijn van Oosterhout wrote:

Otherwise, have you VACUUM ANALYZEd recently?

Argh! That seems to have been it. I have pg_autovacuum running, so it
didn't occur to me.

pg_autovacuum only knows about table activity that has taken place while
it's running. Example: Table A has an analyze threshold of 1000, if
you perform 999 updates on table A, then kill and restart pg_autovacuum,
you will have to perform another 1000 updates before pg_autovacuum
performs an ANALYZE. This is a large limitation of the current
implementation.

I tried to fix this for 8.0, but my autovacuum improvements didn't make
the cut.

Matthew
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Steve Crawford]

On Tuesday 31 August 2004 3:49 pm, Matthew T. O'Connor wrote:

Thomas F.O'Connell wrote:

On Aug 31, 2004, at 4:34 PM, Martijn van Oosterhout wrote:

Otherwise, have you VACUUM ANALYZEd recently?

Argh! That seems to have been it. I have pg_autovacuum running,
so it didn't occur to me.

pg_autovacuum only knows about table activity that has taken place
while it's running. Example: Table A has an analyze threshold of
1000, if you perform 999 updates on table A, then kill and restart
pg_autovacuum, you will have to perform another 1000 updates before
pg_autovacuum performs an ANALYZE. This is a large limitation of
the current implementation.

I tried to fix this for 8.0, but my autovacuum improvements didn't
make the cut.

As a quick-'n'-dirty "fix", what about a command-line option to
autovacuum that tells it to vacuum analyze all tables on startup? At
least it would start in a somewhat known condition.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Matthew T. O'Connor]

Steve Crawford wrote:

On Tuesday 31 August 2004 3:49 pm, Matthew T. O'Connor wrote:

pg_autovacu um only knows about table activity that has taken place
while it's running. Example: Table A has an analyze threshold of
1000, if you perform 999 updates on table A, then kill and restart
pg_autovacuum , you will have to perform another 1000 updates before
pg_autovacu um performs an ANALYZE. This is a large limitation of
the current implementation.

I tried to fix this for 8.0, but my autovacuum improvements didn't
make the cut.

As a quick-'n'-dirty "fix", what about a command-line option to
autovacuum that tells it to vacuum analyze all tables on startup? At
least it would start in a somewhat known condition.

I thought having pg_autovacuum do that, but seemed like overkill, but
perhaps a command line option wouldn't be a bad thing. However, you
could also just as easily do a manual vacuum analyze whenever you like.

Matthew

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match