Thread: Questions about indexes

Hi everyone !

I have some little questions about indexes :

Does PostgreSQL create an index when a table is created?
Does it create index even if there is no PK for this table?
If yes, why?
I know that indexes increase performance for SELECT but it is the
contrary when INSERT because the index must be actualised every time,
isn't it?
How can I see all the index that PostgreSQL have implicitly create?
How can I delete them in keeping integrity in the database?

If someone could answer these questions, I thank him in advance.

Sincerly, Renaud THONNART

On Wed, May 09, 2001 at 10:31:27AM +0200, Renaud Thonnart wrote:

> Does PostgreSQL create an index when a table is created?

 Yes, with PK.

> Does it create index even if there is no PK for this table?

 No.

> I know that indexes increase performance for SELECT but it is the
> contrary when INSERT because the index must be actualised every time,
> isn't it?

 Yes.

> How can I see all the index that PostgreSQL have implicitly create?

 You can, from psql, do:

     # \d table_name

 This order shows all info about a table, including existing indexes.

> How can I delete them in keeping integrity in the database?

 drop index?...

 Greets.

                 David

Thank you for your explaination, David !

Renaud THONNART

At 11:10 AM 5/9/01 +0200, DaVinci wrote:
>On Wed, May 09, 2001 at 10:31:27AM +0200, Renaud Thonnart wrote:
>> How can I delete them in keeping integrity in the database?
>
> drop index?...

Dropping the index will not maintain the PK integrity...

http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-createtable
.html
says:
PRIMARY KEY Constraint
Notes:
Postgres automatically creates a unique index to assure data integrity (see
CREATE INDEX statement).

Frank