Database Reference
In-Depth Information
SP-GiST
Space-Partitioning Trees Generalized Search Tree (SP-GiST), introduced in version
9.2, can be used in the same situations as GiST but can be faster for certain kinds
of data distribution. PostgreSQL's native geometric data types, such as point and
box , and the text data type, were the first to support SP-GiST. In version 9.3, support
extended to range types. The PostGIS spatial extension also has plans to take ad‐
vantage of this specialized index in the near future.
hash
Hash indexes were popular prior to the advent of GiST and GIN. General consensus
rates GiST and GIN above hash in terms of both performance and transaction safety.
The write-ahead log does not track hash indexes; therefore, you can't use them in
streaming replication setups. PostgreSQL has relegated hash to legacy status. You
may still encounter this index type in other databases, but it's best to eschew hash
in PostgreSQL.
B-Tree-GiST/B-Tree-GIN
If you want to explore stock beyond what PostgreSQL installs by default, either out
of need or curiosity, start with the composite B-Tree-GiST or B-Tree-GIN indexes,
both available as extensions.
These hybrids support the specialized operators of GiST or GIN, but also offers
indexablity of the equality operator in B-Tree indexes. You'll find them indispen‐
sable when you want to create a compound index composed of multiple columns
with data types like character varying or number —normally serviced by equality
operators—or like a hierarchical ltree type or full-text vector with operators sup‐
ported only by GIN/GiST.
Operator Classes
We would have loved to skip this section on operator classes. Many of you will sail
through your index-capades without ever needing to know what they are and why they
matter for indexes. But if you falter, you'll need to understand operator classes to trou‐
bleshoot the perennial question, “Why is the planner not taking advantage of my index?”
Algorithm experts intend for their indexes to work against certain data types and com‐
parison operators. An expert in indexing ranges could obsess over the overlap operator
( && ), whereas an expert inventing indexes for faster text search may find little meaning
in an overlap. A computational linguist trying to index Chinese or other logographic
languages probably has little use for inequalities, whereas A-to-Z sorting is critical for
an alphabetical writing system.
PostgreSQL groups comparison operators that are similar and permissible data types
into operator classes (opclass for short). For example, the int4_ops operator class in‐
cludes the operators = < > > < to be applied against the data type of int4 . The pg_op
Search WWH ::




Custom Search