PostgreSQL Discussion - PRIMARY KEY and indexes

Author

PRIMARY KEY and indexes

Craig Bryden

2005-04-23, 7:23 am

Hi

I come from a MS-SQL background and I'm trying to get some clarity on
indexes in Postgres. If anyone can help with the following questions it
would be much appreciated:

1. When creating a primary key contraint on a table, is an equivalent index
automatically created for you, or would I have to create an index manually?
2. How do I create a clustered index in postgres?

Thanks
Craig

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sean Davis

2005-04-23, 7:23 am

----- Original Message -----
From: "Craig Bryden" <postgresql@bryden.co.za>
To: "pgsql" <pgsql-general@postgresql.org>
Sent: Saturday, April 23, 2005 5:45 AM
Subject: [GENERAL] PRIMARY KEY and indexes

> Hi
>
> I come from a MS-SQL background and I'm trying to get some clarity on
> indexes in Postgres. If anyone can help with the following questions it
> would be much appreciated:
>
> 1. When creating a primary key contraint on a table, is an equivalent
> index
> automatically created for you, or would I have to create an index
> manually?

I think if you try it:

CREATE TABLE pk_idx_test (
id INT PRIMARY KEY
);

You will get:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"pk_idx_test_pkey" for table "pk_idx_test"

Query returned successfully with no result in 266 ms.

> 2. How do I create a clustered index in postgres?

See the manual here:
http://www.postgresql.org/docs/8.0/...tive/index.html
and specifically here:
http://www.postgresql.org/docs/8.0/...ql-cluster.html
http://www.postgresql.org/docs/8.0/...altertable.html

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

Alvaro Herrera

2005-04-23, 11:23 am

On Sat, Apr 23, 2005 at 11:45:43AM +0200, Craig Bryden wrote:

> 2. How do I create a clustered index in postgres?

There is no such thing as a clustered index in Postgres. You can order
the table itself following an index (that's what CLUSTER does), but you
don't get the data itself inside the index, which is what AFAIK a
clustered index in other database systems.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl> )
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)

---------------------------(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

Joshua D. Drake

2005-04-23, 11:23 am

Alvaro Herrera wrote:
> On Sat, Apr 23, 2005 at 11:45:43AM +0200, Craig Bryden wrote:
>
>
>
>
> There is no such thing as a clustered index in Postgres. You can order
> the table itself following an index (that's what CLUSTER does), but you
> don't get the data itself inside the index, which is what AFAIK a
> clustered index in other database systems.

I believe this is being worked on for 8.1 yes?

Sincerely,

Joshua D. Drake
Command Prompt, Inc.

>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera

2005-04-23, 11:23 am

On Sat, Apr 23, 2005 at 08:45:25AM -0700, Joshua D. Drake wrote:
> Alvaro Herrera wrote:
>
> I believe this is being worked on for 8.1 yes?

I haven't heard of such a thing ... Maybe the bizgres people wants to
do that. But it's hard to do so I wouldn't expect a usable
implementation for 8.1 if they are just starting.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl> )
"I personally became interested in Linux while I was dating an English major
who wouldn't know an operating system if it walked up and bit him."
(Val Henson)

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

Sponsored Links