In an index scan, the index access method is responsible for regurgitating the TIDs of all the tuples it has been told about that match the scan keys. The access method is not involved in actually fetching those tuples from the index's parent table, nor in determining whether they pass the scan's time qualification test or other conditions. A scan key is the internal representation of a WHERE clause of the form index_key operator constant, where the index key is one of the columns of the index

Index access methods must handle concurrent updates of the index by multiple processes. The core PostgreSQL system obtains AccessShareLock on the index during an index scan, and RowExclusiveLock when updating the index (including plain VACUUM). Since these lock types do not conflict, the access method is responsible for handling any fine-grained locking it might need. An exclusive lock on the index as a whole will be taken only during index creation, destruction, or REINDEX. Building an index t

The amcostestimate function is given information describing a possible index scan, including lists of WHERE and ORDER BY clauses that have been determined to be usable with the index. It must return estimates of the cost of accessing the index and the selectivity of the WHERE clauses (that is, the fraction of parent-table rows that will be retrieved during the index scan). For simple cases, nearly all the work of the cost estimator can be done by calling standard routines in the optimizer; the

The index construction and maintenance functions that an index access method must provide in IndexAmRoutine are: IndexBuildResult * ambuild (Relation heapRelation, Relation indexRelation, IndexInfo *indexInfo); Build a new index. The index relation has been physically created, but is empty. It must be filled in with whatever fixed data the access method requires, plus entries for all tuples already existing in the table. Ordinarily the ambuild function will call IndexBuildHe

Each index access method is described by a row in the pg_am system catalog. The pg_am entry specifies a name and a handler function for the access method. These entries can be created and deleted using the CREATE ACCESS METHOD and DROP ACCESS METHOD SQL commands. An index access method handler function must be declared to accept a single argument of type internal and to return the pseudo-type index_am_handler. The argument is a dummy value that simply serves to prevent handler functions from be

61.4.1. GiST buffering build Building large GiST indexes by simply inserting all the tuples tends to be slow, because if the index tuples are scattered across the index and the index is large enough to not fit in cache, the insertions need to perform a lot of random I/O. Beginning in version 9.2, PostgreSQL supports a more efficient method to build GiST indexes based on buffering, which can dramatically reduce the number of random I/Os needed for non-ordered data sets. For well-ordered data se

Traditionally, implementing a new index access method meant a lot of difficult work. It was necessary to understand the inner workings of the database, such as the lock manager and Write-Ahead Log. The GiST interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The GiST layer itself takes care of concurrency, logging and searching the tree structure. This extensibility should not be confused with the ex

The PostgreSQL source distribution includes several examples of index methods implemented using GiST. The core system currently provides text search support (indexing for tsvector and tsquery) as well as R-Tree equivalent functionality for some of the built-in geometric data types (see src/backend/access/gist/gistproc.c). The following contrib modules also contain GiST operator classes: btree_gist B-tree equivalent functionality for several data types cube Indexing for multidimensional cube

The core PostgreSQL distribution includes the GiST operator classes shown in Table 61-1. (Some of the optional modules described in Appendix F provide additional GiST operator classes.) Table 61-1. Built-in GiST Operator Classes Name Indexed Data Type Indexable Operators Ordering Operators box_ops box && &> &< &<| >> << <<| <@ @> @ |&> |>> ~ ~= circle_ops circle && &> &< &<| >> << &

GiST stands for Generalized Search Tree. It is a balanced, tree-structured access method, that acts as a base template in which to implement arbitrary indexing schemes. B-trees, R-trees and many other indexing schemes can be implemented in GiST. One advantage of GiST is that it allows the development of custom data types with the appropriate access methods, by an expert in the domain of the data type, rather than a database expert. Some of the information here is derived from the University of