建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.我自己把它解釋成當一個query同時帶多個條件時,composite index可以加快查詢的速度。
All the columns in a composite index must be in the same table.
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;
沒有留言:
張貼留言