Pivotal GemFire® v8.0

Tips and Guidelines on Using Indexes

Tips and Guidelines on Using Indexes

Optimizing your queries with indexes requires a cycle of careful planning, testing, and tuning. Poorly-defined indexes can degrade the performance of your queries instead of improving it. This section gives guidelines for index usage in the query service.

When creating indexes, keep in mind the following:

  • Indexes incur maintenance costs as they must be updated when the indexed data changes. An index that requires many updates and is not used very often may require more system resources than using no index at all.
  • Indexes consume memory.
  • Indexes have limited support on overflow regions. See Using Indexes with Overflow Regions for details.

Tips for Writing Queries that Use Indexes

As with query processors that run against relational databases, the way a query is written can greatly affect execution performance. Among other things, whether indexes are used depends on how each query is stated. These are some of the things to consider when optimizing your GemFire queries for performance:

  • In general an index will improve query performance if the FROM clauses of the query and index match exactly.
  • The query evaluation engine does not have a sophisticated cost-based optimizer. It has a simple optimizer which selects best index (one) or multiple indexes based on the index size and the operator that is being evaluated.
  • For AND operators, you may get better results if the conditions that use indexes and conditions that are more selective come before other conditions in the query.
  • Indexes are not used in expressions that contain NOT, so in a WHERE clause of a query, qty >= 10 could have an index on qty applied for efficiency. However, NOT(qty < 10) could not have the same index applied.