Automatically created indexes in postgresql: combined index on key attributes?

Question

I'm not sure which indexes postgresql AUTOMATICALLY creates; I think it will create one on Book(ibsn), because its the primary key, and also Book(title), because its a candidate key... but I'm not sure if postgres will automatically create a combined index on all key attributes Book(ibsn, title). Also, would it create any other indexes automatically?

CREATE TABLE Book (
 isbn       INTEGER CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000),
 title      VARCHAR(200) CONSTRAINT B_TITLE NOT NULL UNIQUE,
 author     VARCHAR(50) CONSTRAINT B_AUTH NOT NULL,
 cost       FLOAT DEFAULT 0.00,
 lent_date  DATE,
 returnDate DATE,
 times_lent INTEGER,
 sectionID  SMALLINT,
 CONSTRAINT BOOK_PRIME PRIMARY KEY (isbn),
 CONSTRAINT BOOK_SECT FOREIGN KEY (sectionID) REFERENCES Section(sectionID) ON DELETE CASCADE
  );

user330315 · Accepted Answer · 2016-05-11 06:11:09Z

1

Postgres will automatically create indexes only for:

primary keys: Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key
unique constraints: Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint

In your case, Postgres creates one unique index on the column isbn and one unique index on the column unique because you declared each column individually to be unique, not the combination of both.

Collectives™ on Stack Overflow

Automatically created indexes in postgresql: combined index on key attributes?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related