Re: clustered index benchmark comparing Postgresql vs Mariadb

유상지 schrieb am 31.08.2017 um 04:03:
> Cluster secondary indexes were faster than those without cluster indexes in pg, but slower than mariadb.

There is no such thing as a "clustered index" in Postgres.

The Postgres "cluster" command physically sorts the rows of a table according to the sort order of an index, but that is something completely different then a "clustered index". The data is still stored in the index and the table.

A clustered index in MariaDB/MySQL stores the entire table data. So the table and the index is the same thing (Oracle calls that an "index organized table" which describes this a lot better). As the table and index are the same thing you can't have two clustered indexes on the same table.

An index in Postgres only stores the data of the indexed columns (plus an internal row identifier). There is no technical difference between a primary key index and any other index. The structure and storage is always the same. So the term "secondary index" does not really make sense in Postgres.