{"Entry":{"collection":"indexam","key":"btree","name":"B-tree","aliases":["b-tree","balanced tree","range","sorting","unique"],"metadata":{"aliases":["b-tree","balanced tree","range","sorting","unique"],"category":"Ordered search","content_hash":"0e31f21a5a78912dc7eb484e6af0a196f08d7d1f9c6c3fabec9a5c61768766d2","derived_content_hash":"a964a83ec7c8c20dd3345f0ea9c3167e9ba906bdca822d058ee1e997b88062ad","imported_at":"2026-09-30T00:40:37.183691+08:00","name":"B-tree","name_zh":"B-tree","slug":"btree","summary":"B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:"}},"Definition":{"Collection":"indexam","Key":"btree","SourceDatabase":"center","Version":"18","SourceTable":"index_am","SourceKey":"btree","SourceRevision":"0e39ebd4fddbd990280e62f4503f0bd18765bbd990dccf4cba976b5d47da45e7","Facts":{"capabilities":[{"evidence":[{"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"},{"evidence":[{"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"},{"evidence":[{"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"},{"evidence":[{"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"},{"evidence":[{"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"},{"evidence":[],"key":"distance","label":"Distance ordering","note":"Availability depends on the chosen operator class and ordering operator.","state":"unknown","value":"Not established"},{"evidence":[{"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"},{"evidence":[{"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"}],"description":["B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:"],"description_zh":"btree 索引访问方法。","description_zh_html":"\u003cp\u003ebtree 索引访问方法。\u003c/p\u003e\u003cdl\u003e\u003cdt\u003e有序输出\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003e唯一键\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003e多键列\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003eINCLUDE 列\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003e仅索引扫描\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003e距离排序\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e未确定\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003e并行索引扫描\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003cdt\u003e并行索引构建\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003e是\u003c/code\u003e\u003c/dd\u003e\u003c/dl\u003e\u003ch3\u003e同版本中文手册节选\u003c/h3\u003e\u003cp\u003e\n   B-树能够处理可以按某种顺序排序的数据上的等值查询和范围查询。特别是，只要已索引列参与了下列任一操作符的比较，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 查询规划器就会考虑使用 B-树索引：\n\n\u003c/p\u003e\u003cp\u003e\n\n   与这些操作符组合等价的构造，例如 \u003ccode class=\"literal\"\u003eBETWEEN\u003c/code\u003e 和 \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e，也可以通过 B-树索引搜索来实现。此外，索引列上的 \u003ccode class=\"literal\"\u003eIS NULL\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eIS NOT\n   NULL\u003c/code\u003e 条件也可以配合 B-树索引使用。\n  \u003c/p\u003e\u003cp\u003e\n   如果模式是常量，并且锚定在字符串起始位置，优化器也可以对涉及模式匹配操作符 \u003ccode class=\"literal\"\u003eLIKE\u003c/code\u003e 和\u003ccode class=\"literal\"\u003e~\u003c/code\u003e的查询使用 B-树索引，例如 \u003ccode class=\"literal\"\u003ecol LIKE\n   'foo%'\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003ecol ~ '^foo'\u003c/code\u003e，但不能用于 \u003ccode class=\"literal\"\u003ecol LIKE '%bar'\u003c/code\u003e。不过，如果你的数据库没有使用 C 区域设置，就需要以一个特殊操作符类来创建该索引，才能支持模式匹配查询的索引化，详见下文\u003ca class=\"xref\" href=\"/zh/docs/18/indexes-opclass.html\"\u003e第 11.10 节\u003c/a\u003e。B-树索引也可以用于 \u003ccode class=\"literal\"\u003eILIKE\u003c/code\u003e 和\u003ccode class=\"literal\"\u003e~*\u003c/code\u003e，但前提是模式以非字母字符开头，也就是不会受到大小写转换影响的字符。\n  \u003c/p\u003e\u003cp\u003e\n   B-树索引还可以用于按排序顺序取回数据。这并不总是比简单扫描再排序更快，但通常会很有帮助。\n  \u003c/p\u003e\u003cp\u003e\u003ca href=\"/zh/docs/18/indexes-types.html#INDEXES-TYPES-BTREE\"\u003e查看完整中文手册与上下文\u003c/a\u003e。\u003c/p\u003e","evidence_kind":"documentation","facts":[{"label":"Sorted output","value":"Yes"},{"label":"Unique keys","value":"Yes"},{"label":"Multiple key columns","value":"Yes"},{"label":"INCLUDE columns","value":"Yes"},{"label":"Index-only scans","value":"Yes"},{"label":"Distance ordering","value":"Not established"},{"label":"Parallel index scan","value":"Yes"},{"label":"Parallel index build","value":"Yes"}],"facts_zh":[{"label":"有序输出","value":"支持"},{"label":"唯一键","value":"支持"},{"label":"多键列","value":"支持"},{"label":"INCLUDE 附加列","value":"支持"},{"label":"仅索引扫描","value":"支持"},{"label":"按距离排序","value":"未确定"},{"label":"并行索引扫描","value":"支持"},{"label":"并行构建索引","value":"支持"}],"related":[{"label":"11.10. Operator Classes and Operator Families","url":"/docs/18/indexes-opclass.html"},{"label":"pg_am","url":"/wiki/catalog/pg_am/?v=18"},{"label":"pg_opclass","url":"/wiki/catalog/pg_opclass/?v=18"},{"label":"pg_opfamily","url":"/wiki/catalog/pg_opfamily/?v=18"},{"label":"pg_amop","url":"/wiki/catalog/pg_amop/?v=18"},{"label":"pg_amproc","url":"/wiki/catalog/pg_amproc/?v=18"},{"label":"CREATE INDEX","url":"/wiki/sql/create-index/?v=18"},{"label":"Operator classes and families","url":"/docs/18/indexes-opclass.html"},{"label":"Index access method interface","url":"/docs/18/indexam.html"},{"label":"Storage parameters","url":"/wiki/relopts/?v=18"}],"release":{"channel":"stable","evidence_kind":"English manual extraction","label":"18.6","major":"18","manifest":{"major":"18","release":"18.6","source_mode":"en SGML built with pinned official archive","source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"ref":"PostgreSQL 18.6 Documentation","revision":"0e39ebd4fddbd990280e62f4503f0bd18765bbd990dccf4cba976b5d47da45e7"},"runtime_verified":false,"sections":[{"blocks":[{"paragraphs":["B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:"]},{"code":"\u003c   \u003c=   =   \u003e=   \u003e","paragraphs":[]},{"paragraphs":["Constructs equivalent to combinations of these operators, such as BETWEEN and IN , can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index."]},{"paragraphs":["The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo' , but not col LIKE '%bar' . However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.10 below. It is also possible to use B-tree indexes for ILIKE and ~* , but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion."]},{"paragraphs":["B-tree indexes can also be used to retrieve data in sorted order. This is not always faster than a simple scan and sort, but it is often helpful."]}],"title":"Queries, operators and limitations"}],"sections_zh":[{"blocks":[{"paragraphs":["B-tree 能对可按某种顺序排序的数据执行等值查询和范围查询。具体而言，当索引列参与使用下列运算符之一的比较时，PostgreSQL 查询规划器会考虑使用 B-tree 索引："]},{"code":"\u003c   \u003c=   =   \u003e=   \u003e","paragraphs":[]},{"paragraphs":["等价于这些运算符组合的表达式，例如 BETWEEN 和 IN，也可通过 B-tree 索引搜索实现。索引列上的 IS NULL 或 IS NOT NULL 条件也能使用 B-tree 索引。"]},{"paragraphs":["如果模式是常量，并且锚定在字符串起始位置，优化器也可以对涉及模式匹配操作符 LIKE 和 ~ 的查询使用 B-树索引，例如 col LIKE 'foo%' 或 col ~ '^foo' ，但不能用于 col LIKE '%bar' 。不过，如果你的数据库没有使用 C 区域设置，就需要以一个特殊操作符类来创建该索引，才能支持模式匹配查询的索引化，详见下文 第 11.10 节 。B-树索引也可以用于 ILIKE 和 ~* ，但前提是模式以非字母字符开头，也就是不会受到大小写转换影响的字符。"]},{"paragraphs":["B-tree 索引也可按排序顺序检索数据。这不总是比直接扫描后排序更快，但通常有帮助。"]}],"title":"查询、操作符与限制"}],"signature":"CREATE INDEX name ON table_name USING btree (column_name);","sources":[{"anchor":"INDEXES-TYPES-BTREE","file":"indexes-types.html","label":"PostgreSQL 18.6: B-tree","sha256":"cee340145a2ef5fbd807f66755b10d22bb049e328c460f572f45ad6193e7e4a1","url":"/docs/18/indexes-types.html#INDEXES-TYPES-BTREE"},{"anchor":"","file":"catalog-pg-am.html","label":"PostgreSQL 18.6 · catalog-pg-am","sha256":"579364179365a51945e2edb6481df5d198916c2c725e506f166824dedf08994c","url":"/docs/18/catalog-pg-am.html"},{"anchor":"","file":"hash-index.html","label":"PostgreSQL 18.6 · hash-index","sha256":"b994550220e1ee05648d09804c9eb2302834e07e261932420a96605d7c9c6140","url":"/docs/18/hash-index.html"},{"anchor":"","file":"indexam.html","label":"PostgreSQL 18.6 · indexam","sha256":"0a38f7c0ac0541f31e086c83a624e782c15367456578a6dd3329882516bc1080","url":"/docs/18/indexam.html"},{"anchor":"","file":"indexes-index-only-scans.html","label":"PostgreSQL 18.6 · indexes-index-only-scans","sha256":"2322ec12b82f3fd01ee4072049caaa5a0190148ac68d98c0fcd6eda958828a83","url":"/docs/18/indexes-index-only-scans.html"},{"anchor":"","file":"indexes-multicolumn.html","label":"PostgreSQL 18.6 · indexes-multicolumn","sha256":"c0a799ec49b8ef057a63d465fb1d72a3b81e16d652c28a71734d45592d081202","url":"/docs/18/indexes-multicolumn.html"},{"anchor":"","file":"indexes-opclass.html","label":"PostgreSQL 18.6 · indexes-opclass","sha256":"07de06abf3a2aff2a891a2789def434d9b76705e54d45ca5338dc13451ae2962","url":"/docs/18/indexes-opclass.html"},{"anchor":"","file":"indexes-ordering.html","label":"PostgreSQL 18.6 · indexes-ordering","sha256":"85fac99c7c66ed19dd021f12b5cc53ce18aa5bfa4b56c8c168317f99b8f12d3b","url":"/docs/18/indexes-ordering.html"},{"anchor":"","file":"indexes-unique.html","label":"PostgreSQL 18.6 · indexes-unique","sha256":"7c3eac7fa180b22534ff01df0c68155f15b6b360ffe35507c5266059116840db","url":"/docs/18/indexes-unique.html"},{"anchor":"","file":"parallel-plans.html","label":"PostgreSQL 18.6 · parallel-plans","sha256":"62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070","url":"/docs/18/parallel-plans.html"},{"anchor":"","file":"sql-createindex.html","label":"PostgreSQL 18.6 · sql-createindex","sha256":"6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580","url":"/docs/18/sql-createindex.html"}],"storage_options":[{"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":{"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"},{"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":{"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"}],"tables_zh":[],"zh_from":{"authored_translation":"source-derived-structured-summary","body_sha256":"8cbb58dface4e534ba8a62df2281704c2a3747d62afab0f6f9d4af14d9e8a683","kind":"same-version-chinese-manual-with-source-derived-structured-summary","manual":{"anchor":"INDEXES-TYPES-BTREE","build_id":107,"extraction":"concept-introduction","fragment_sha256":"4fa43425a416f319f750bd9e62348fd7dceac92218444b223053f06ca5d60849","locale":"zh-Hans","page_key":"indexes-types.html","source_hash":"88d19a31beb78d185e353fcc53ddc8d44f9f2f55b5627361a271bbbb2d20cf80","source_revision":"unknown:rendered-translation-baseline","translated_from_revision":null,"translation_revision":"git:b1b500221dbe5437cfca004d6b04d3f6e422765d;html-sha256:08e987a66af27b98444a8057c256314eafa13611a3fb9e888214fc436824244b","url":"/zh/docs/18/indexes-types.html#INDEXES-TYPES-BTREE","version":"18"},"runtime_rerun":false,"source_facts_revision":"0e39ebd4fddbd990280e62f4503f0bd18765bbd990dccf4cba976b5d47da45e7"}},"ManualEvidence":{"release":{"channel":"stable","evidence_kind":"English manual extraction","label":"18.6","major":"18","manifest":{"major":"18","release":"18.6","source_mode":"en SGML built with pinned official archive","source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"ref":"PostgreSQL 18.6 Documentation","revision":"0e39ebd4fddbd990280e62f4503f0bd18765bbd990dccf4cba976b5d47da45e7"},"sources":[{"anchor":"INDEXES-TYPES-BTREE","file":"indexes-types.html","label":"PostgreSQL 18.6: B-tree","sha256":"cee340145a2ef5fbd807f66755b10d22bb049e328c460f572f45ad6193e7e4a1","url":"/docs/18/indexes-types.html#INDEXES-TYPES-BTREE"},{"anchor":"","file":"catalog-pg-am.html","label":"PostgreSQL 18.6 · catalog-pg-am","sha256":"579364179365a51945e2edb6481df5d198916c2c725e506f166824dedf08994c","url":"/docs/18/catalog-pg-am.html"},{"anchor":"","file":"hash-index.html","label":"PostgreSQL 18.6 · hash-index","sha256":"b994550220e1ee05648d09804c9eb2302834e07e261932420a96605d7c9c6140","url":"/docs/18/hash-index.html"},{"anchor":"","file":"indexam.html","label":"PostgreSQL 18.6 · indexam","sha256":"0a38f7c0ac0541f31e086c83a624e782c15367456578a6dd3329882516bc1080","url":"/docs/18/indexam.html"},{"anchor":"","file":"indexes-index-only-scans.html","label":"PostgreSQL 18.6 · indexes-index-only-scans","sha256":"2322ec12b82f3fd01ee4072049caaa5a0190148ac68d98c0fcd6eda958828a83","url":"/docs/18/indexes-index-only-scans.html"},{"anchor":"","file":"indexes-multicolumn.html","label":"PostgreSQL 18.6 · indexes-multicolumn","sha256":"c0a799ec49b8ef057a63d465fb1d72a3b81e16d652c28a71734d45592d081202","url":"/docs/18/indexes-multicolumn.html"},{"anchor":"","file":"indexes-opclass.html","label":"PostgreSQL 18.6 · indexes-opclass","sha256":"07de06abf3a2aff2a891a2789def434d9b76705e54d45ca5338dc13451ae2962","url":"/docs/18/indexes-opclass.html"},{"anchor":"","file":"indexes-ordering.html","label":"PostgreSQL 18.6 · indexes-ordering","sha256":"85fac99c7c66ed19dd021f12b5cc53ce18aa5bfa4b56c8c168317f99b8f12d3b","url":"/docs/18/indexes-ordering.html"},{"anchor":"","file":"indexes-unique.html","label":"PostgreSQL 18.6 · indexes-unique","sha256":"7c3eac7fa180b22534ff01df0c68155f15b6b360ffe35507c5266059116840db","url":"/docs/18/indexes-unique.html"},{"anchor":"","file":"parallel-plans.html","label":"PostgreSQL 18.6 · parallel-plans","sha256":"62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070","url":"/docs/18/parallel-plans.html"},{"anchor":"","file":"sql-createindex.html","label":"PostgreSQL 18.6 · sql-createindex","sha256":"6aa00da55815d13d0355dd25cb1cdb603fa36964993f164d1b6f486011282580","url":"/docs/18/sql-createindex.html"}]},"MeasuredEvidence":{"runtime_verified":false}},"Text":{"Collection":"indexam","Key":"btree","SourceDatabase":"center","Version":"18","Locale":"en","Title":"B-tree","Summary":"B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:","BodyHTML":"\u003cp\u003eB-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:\u003c/p\u003e","SourceRevision":"0e39ebd4fddbd990280e62f4503f0bd18765bbd990dccf4cba976b5d47da45e7","ContentHash":"38762dd7a2b8732fbaf9c36f09819dfc953036100afb6d5e770c94670c8f6a88","Payload":{"description":["B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:"],"related":[{"label":"11.10. Operator Classes and Operator Families","url":"/docs/18/indexes-opclass.html"},{"label":"pg_am","url":"/wiki/catalog/pg_am/?v=18"},{"label":"pg_opclass","url":"/wiki/catalog/pg_opclass/?v=18"},{"label":"pg_opfamily","url":"/wiki/catalog/pg_opfamily/?v=18"},{"label":"pg_amop","url":"/wiki/catalog/pg_amop/?v=18"},{"label":"pg_amproc","url":"/wiki/catalog/pg_amproc/?v=18"},{"label":"CREATE INDEX","url":"/wiki/sql/create-index/?v=18"},{"label":"Operator classes and families","url":"/docs/18/indexes-opclass.html"},{"label":"Index access method interface","url":"/docs/18/indexam.html"},{"label":"Storage parameters","url":"/wiki/relopts/?v=18"}],"sections":[{"blocks":[{"paragraphs":["B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:"]},{"code":"\u003c   \u003c=   =   \u003e=   \u003e","paragraphs":[]},{"paragraphs":["Constructs equivalent to combinations of these operators, such as BETWEEN and IN , can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index."]},{"paragraphs":["The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo' , but not col LIKE '%bar' . However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.10 below. It is also possible to use B-tree indexes for ILIKE and ~* , but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion."]},{"paragraphs":["B-tree indexes can also be used to retrieve data in sorted order. This is not always faster than a simple scan and sort, but it is often helpful."]}],"title":"Queries, operators and limitations"}]}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}