B-tree

capabilities

map[evidence:[map[anchor:INDEXES-ORDERING file:indexes-ordering.html label:PostgreSQL 18.6 · indexes-ordering quote:In addition to simply finding the rows to be returned by a query, an index may be able to deliver them in a specific sorted order. This allows a query's ORDER BY specification to be honored without a separate sorting step. Of the index types currently supported by PostgreSQL , only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order. sha256:85fac99c7c66ed19dd021f12b5cc53ce18aa5bfa4b56c8c168317f99b8f12d3b url:/docs/18/indexes-ordering.html#INDEXES-ORDERING]] key:ordered label:Sorted output note:Ordinary sorted output is distinct from ordering by an operator, such as nearest-neighbor distance. state:yes value:Yes], map[evidence:[map[anchor:INDEXES-UNIQUE file:indexes-unique.html label:PostgreSQL 18.6 · indexes-unique quote:Currently, only B-tree indexes can be declared unique. sha256:7c3eac7fa180b22534ff01df0c68155f15b6b360ffe35507c5266059116840db url:/docs/18/indexes-unique.html#INDEXES-UNIQUE]] key:unique label:Unique keys note:This means a unique index. Exclusion constraints use a different contract. state:yes value:Yes], map[evidence:[map[anchor:INDEXES-MULTICOLUMN file:indexes-multicolumn.html label:PostgreSQL 18.6 · indexes-multicolumn quote:Currently, only the B-tree, GiST, GIN, and BRIN index types support multiple-key-column indexes. Whether there can be multiple key columns is independent of whether INCLUDE columns can be added to the index. Indexes can have up to 32 columns, including INCLUDE columns. (This limit can be altered when building PostgreSQL ; see the file pg_config_manual.h .) sha256:c0a799ec49b8ef057a63d465fb1d72a3b81e16d652c28a71734d45592d081202 url:/docs/18/indexes-multicolumn.html#INDEXES-MULTICOLUMN]] key:multicolumn label:Multiple key columns note:Multiple search keys are distinct from non-key INCLUDE payload columns. state:yes value:Yes], map[evidence:[map[anchor:SQL-CREATEINDEX file:sql-createindex.html label:PostgreSQL 18.6 · sql-createindex quote:Currently, the B-tree, GiST and SP-GiST index access methods support this feature. In these indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation. sha256:6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580 url:/docs/18/sql-createindex.html#SQL-CREATEINDEX]] key:include label:INCLUDE columns note:Payload columns do not become search keys. Wide payloads can exceed the index tuple-size limit. state:yes value:Yes], map[evidence:[map[anchor:INDEXES-INDEX-ONLY-SCANS file:indexes-index-only-scans.html label:PostgreSQL 18.6 · indexes-index-only-scans quote:The index type must support index-only scans. B-tree indexes always do. GiST and SP-GiST indexes support index-only scans for some operator classes but not others. Other index types have no support. The underlying requirement is that the index must physically store, or else be able to reconstruct, the original data value for each index entry. As a counterexample, GIN indexes cannot support index-only scans because each index entry typically holds only part of the original data value. sha256:2322ec12b82f3fd01ee4072049caaa5a0190148ac68d98c0fcd6eda958828a83 url:/docs/18/indexes-index-only-scans.html#INDEXES-INDEX-ONLY-SCANS]] key:index_only label:Index-only scans note:The query must use covered values; visibility-map state determines whether heap visits can be avoided. state:yes value:Yes], map[evidence:[] key:distance label:Distance ordering note:Availability depends on the chosen operator class and ordering operator. state:unknown value:Not established], map[evidence:[map[anchor:PARALLEL-SCANS file:parallel-plans.html label:PostgreSQL 18.6 · parallel-plans quote:In a parallel index scan or parallel index-only scan , the cooperating processes take turns reading data from the index. Currently, parallel index scans are supported only for btree indexes. Each process will claim a single index block and will scan and return all tuples referenced by that block; other processes can at the same time be returning tuples from a different index block. The results of a parallel btree scan are returned in sorted order within each worker process. sha256:62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070 url:/docs/18/parallel-plans.html#PARALLEL-SCANS]] key:parallel_scan label:Parallel index scan note:A cooperating scan of one index is distinct from a parallel bitmap heap scan or separate serial scans under Parallel Append. state:yes value:Yes], map[evidence:[map[anchor:SQL-CREATEINDEX file:sql-createindex.html label:PostgreSQL 18.6 · sql-createindex quote:PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build . For index methods that support building indexes in parallel (currently, B-tree, GIN, and BRIN), maintenance_work_mem specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any. sha256:6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580 url:/docs/18/sql-createindex.html#SQL-CREATEINDEX]] key:parallel_build label:Parallel index build note:Parallel construction is separate from parallel scans. Worker availability, settings and build phases also matter. state:yes value:Yes]

storage options

map[description:[Controls how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index builds, and also when extending the index at the right (adding new largest key values). If pages subsequently become completely full, they will be split, leading to fragmentation of the on-disk index structure. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. B-tree indexes on tables where many inserts and/or updates are anticipated can benefit from lower fillfactor settings at CREATE INDEX time (following bulk loading into the table). Values in the range of 50 - 90 can usefully “ smooth out ” the rate of page splits during the early life of the B-tree index (lowering fillfactor like this may even lower the absolute number of page splits, though this effect is highly workload dependent). The B-tree bottom-up index deletion technique described in Section 65.1.4.2 is dependent on having some “ extra ” space on pages to store “ extra ” tuple versions, and so can be affected by fillfactor (though the effect is usually not significant). In other specific cases it might be useful to increase fillfactor to 100 at CREATE INDEX time as a way of maximizing space utilization. You should only consider this when you are completely sure that the table is static (i.e. that it will never be affected by either inserts or updates). A fillfactor setting of 100 otherwise risks harming performance: even a few updates or inserts will cause a sudden flood of page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.] name:fillfactor source:map[anchor:INDEX-RELOPTION-FILLFACTOR file:sql-createindex.html label:PostgreSQL 18.6 · sql-createindex sha256:6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580 url:/docs/18/sql-createindex.html#INDEX-RELOPTION-FILLFACTOR] url:/wiki/relopts/btree-fillfactor/?v=18], map[description:[Controls usage of the B-tree deduplication technique described in Section 65.1.4.3 . Set to ON or OFF to enable or disable the optimization. (Alternative spellings of ON and OFF are allowed as described in Section 19.1 .) The default is ON . Turning deduplicate_items off via ALTER INDEX prevents future insertions from triggering deduplication, but does not in itself make existing posting list tuples use the standard tuple representation.] name:deduplicate_items source:map[anchor:INDEX-RELOPTION-DEDUPLICATE-ITEMS file:sql-createindex.html label:PostgreSQL 18.6 · sql-createindex sha256:6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580 url:/docs/18/sql-createindex.html#INDEX-RELOPTION-DEDUPLICATE-ITEMS] url:/wiki/relopts/btree-deduplicate-items/?v=18]