2011/09/26

Postgresql建立索引

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

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



沒有留言:

張貼留言