In PostgreSQL, it is also possible to create an index on an expression such as
LOWER(last_name). This index would be used, for example, to satisfy clauses like
WHERE LOWER(last_name) = 'smith' or ORDER BY LOWER(last_name) ASC. This is a
tradeoff: the expression must be calculated for each row when the index is created,
but that expression is treated as a constant when querying.
You should take a look at your development log to find common queries that are
either being executed too often or take too long. You may find that you have missed
an index. Common places where indexes are omitted are the following:
??? Columns that need to be sorted: position (when using acts_as_list) or any
other natural sort field such as last name, transaction date, or price.
??? Lookup fields other than foreign keys: order number, department code, or
username.
??? Columns that are commonly used in a GROUP BY query??”but be careful that the
indexed attribute has a high enough cardinality (number of unique values) to
make it worth an index.
Full-text indexing
I do not recommend using a DBMS??™s built-in full-text indexing capability. Many
applications will quickly outgrow the limitations of the built-in indexing features.
Repopulating a large full-text index can be slow, and it is better to have indexing
under your control in situations where the database may grow.
The industry leader in full-text search is Ferret, Dave Balmain??™s port of Lucene.
Pages:
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263