Postgresql建立索引

有沒有建索引的查詢速度真的差很多，預設是使用binary-tree來實作index，時間複雜度是O(logN)。從N變成logN，是有指數級的差距！

建index的方法可以參考

• PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE INDEX

以下舉幾個例子，假如我們的table叫account，email這個欄位很常用來當查詢條件，那我們會為email建立index:

postgres=> CREATE INDEX account_email_idx ON account (email);
postgres=> \d account;
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)   # Primary key預設就會建立index了
    "account_email_idx" btree (email)

Composite index?

Composite indexes are used when two or more columns are best searched as a unit or if many queries reference only the columns specified in the index.
All the columns in a composite index must be in the same table.

我自己把它解釋成當一個query同時帶多個條件時，composite index可以加快查詢的速度。

postgres=> CREATE INDEX account_composite_idx ON account (name, email);
postgres=> \d account;
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)
    "account_composite_idx" btree (name, email)

除了建索引，定期清理(VACUUM) database 也可以加快存取速度

postgres=> VACUUM VERBOSE ANALYZE;