Database Reference
In-Depth Information
This kind of functionality needs both the types themselves and overloaded operands,
which know that if you divide distance by time then the result is speed. You will also
need user-defined casts, which are automatically- or manually-invoked conversion
functions between types.
MGRID developed this for use in medical applications where the cost of error can
be high—the difference between 10 ml and 10 cc can be vital. But using a similar
system could also have averted many other disasters, where using wrong units has
ended with producing bad computation results. If the unit is always there together
with the amount, the possibility for these kinds of errors is very much diminished.
You can also add your own index methods if you have some programming skills and
your problem domain is not well served by the existing indexes. There is already a
respectable set of index types included in the core PostgreSQL, as well as several
others which are developed outside the core.
The latest index method which became officially included in PostgreSQL is
KNN
(
K Nearest Neighbor
)—a clever index, which can return K rows ordered by their
distance from the desired search target. One use of KNN is in fuzzy text search,
where this can be used for ranking full-text search results by how well they match the
search terms. Before KNN, this kind of thing was done by querying all rows which
matched even a little, and then sorting all these by the distance function and return-
ing K top rows as the last step.
If done using KNN index, the index access can start returning the rows in the desired
order; so a simple
LIMIT K
function will return you the K top matches.
The KNN index can also be used for real distances, for example answering the re-
quest "Give me the 10 nearest pizza places to central station."
As you see, index types are separate from the data types they index. As another
example, the same
GIN
(
General Inverted Index
) can be used for full-text search
(together with stemmers, thesauri, and other text processing stuff) as well as index-
ing elements of integer arrays.
