14

I'm using PostgreSQL 9.3 version to create database.

I have the following table test with some columns list.

create table test
(
  cola varchar(10),
  colb varchar(10),
  colc varchar(10),
  cold varchar(10)
);

Now I want to create a indexs on some columns.

For example:

I want to create clustered index for columns cola and colb.

And I want to create non clustered index for columns colc and cold.

As I referred this And this ,I come to know that there is no clustered and non clustered index in PostgreSQL.

My Question: What type of index I can use instead of clustered and non clustered index in PostgreSQL,Which does the same job as clustered and non clustered indexes does?

Community
  • 1
  • 1
MAK
  • 6,824
  • 25
  • 74
  • 131
  • 2
    Every index in Postgres is "non-clustered". Postgres does not have clustered indexes.If you run `create index` it will create a (non-clustered) B-Tree index. Why do you think there is no non-clustered index? –  Jan 16 '15 at 06:49
  • @a_horse_with_no_name, Okay! My bad. But how can I create clustered index? – MAK Jan 16 '15 at 06:54
  • 1
    As I said: Postgres doesn't have clustered indexes. So you can't create one. Why do you think you need one? –  Jan 16 '15 at 06:55
  • @a_horse_with_no_name, So `create index` does both job in PostgreSQL. – MAK Jan 16 '15 at 06:57
  • 1
    It **only** creates non-clustered indexes because Postgres does not have a clustered indexes –  Jan 16 '15 at 06:58

2 Answers2

32

My Question: What type of index I can use instead of clustered and non clustered index in PostgreSQL,Which does the same job as clustered and non clustered indexes does?

PostgreSQL doesn't have the concept of clustered indexes at all. Instead, all tables are heap tables and all indexes are non-clustered indexes.

Just create a non-clustered index when you'd usually create a clustered index.

More details:

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • 4
    @mak: I also find this blog from Markus very interesting: http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key –  Jan 16 '15 at 09:35
  • @a_horse_with_no_name, Yeah! It is. – MAK Jan 16 '15 at 09:37
  • 2
    It appears the Postgres implements something that at least approximates a *clustered index*: "CLUSTER instructs PostgreSQL to cluster the table specified by table_name based on the index specified by index_name" From the postgres docs: https://www.postgresql.org/docs/9.1/static/sql-cluster.html – StvnBrkdll Jun 24 '16 at 02:25
  • 1
    @mangotang See [this related question](https://stackoverflow.com/q/47669397/157957). In short, `CLUSTER` reorganises the *current* data in a table based on a particular index, but does not create a different data structure or maintain that order. – IMSoP Dec 06 '17 at 10:37
  • @IMSoP Thank you for the clarification. By saying that postgres "_approximates_ a clustered index", I was attempting to be clear that it is in fact _not_ a clustered index, but _may_ be a viable alternative. – StvnBrkdll Dec 07 '17 at 02:18
  • You are saying PG don't have clustered index, but I see they do https://www.postgresql.org/docs/current/static/sql-cluster.html .. What am I missing? – Arup Rakshit Sep 03 '18 at 18:19
  • @ArupRakshit You are missing the fact that PostgreSQL uses the term cluster for a different concept. PostgreSQL clustering is ordering the rows in a table according to the order defined by an index. Whereas a "clustered index" in some databases refers to the concept of having all columns stored in the index. Similar names, different concepts. – Markus Winand Sep 05 '18 at 04:42
  • @MarkusWinand Indeed, `CLUSTER` does not implement a clustered index because it does not maintain the ordering nor stores the data in the index, however I propose it should be mentioned in your answer since it does have some of the performance gain of clustered indices - as it will dramatically decrease page reads when an index is found. It would benefit both the OP as well as people searching for similar information. – Yuval Sep 21 '18 at 09:19
0

For a clustered index, each of the desired fields must be the primary key. And for the non-clustered index, we act according to the following command :

CREATE INDEX IX_Test_Colc ON test(colc);

CREATE INDEX IX_Test_Cold ON test(cold);
Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Masoud Shakarami
  • 46
  • 3