Cluster and Non cluster index in PostgreSQL

Question

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?

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? — user330315
– user330315, Commented Jan 16, 2015 at 6:49
@a_horse_with_no_name, Okay! My bad. But how can I create clustered index? — MAK
– MAK, Commented Jan 16, 2015 at 6:54
As I said: Postgres doesn't have clustered indexes. So you can't create one. Why do you think you need one? — user330315
– user330315, Commented Jan 16, 2015 at 6:55
@a_horse_with_no_name, So create index does both job in PostgreSQL. — MAK
– MAK, Commented Jan 16, 2015 at 6:57
It only creates non-clustered indexes because Postgres does not have a clustered indexes — user330315
– user330315, Commented Jan 16, 2015 at 6:58

Markus Winand · Accepted Answer · 2015-01-16 07:27:15Z

43

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:

http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index
You might want to read the whole chapter to get proper understanding of these concepts.

answered Jan 16, 2015 at 7:27

Markus Winand

8,8541 gold badge38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user330315 Over a year ago

@mak: I also find this blog from Markus very interesting: use-the-index-luke.com/blog/2014-01/…

MAK Over a year ago

@a_horse_with_no_name, Yeah! It is.

StvnBrkdll Over a year ago

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: postgresql.org/docs/9.1/static/sql-cluster.html

IMSoP Over a year ago

@mangotang See this related question. 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.

StvnBrkdll Over a year ago

@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.

|

Thomas Smyth · Accepted Answer · 2022-11-10 13:39:15Z

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);

edited Nov 10, 2022 at 13:39

Thomas Smyth

5,6546 gold badges27 silver badges37 bronze badges

answered Nov 4, 2022 at 13:59

Masoud Shakarami

543 bronze badges

Collectives™ on Stack Overflow

Cluster and Non cluster index in PostgreSQL

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related