Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNaEW-0004mz-CY for pgsql-hackers@arkaria.postgresql.org; Wed, 30 Sep 2020 11:25:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kNaEV-0002zX-5j for pgsql-hackers@arkaria.postgresql.org; Wed, 30 Sep 2020 11:25:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNaET-0002yO-DS for pgsql-hackers@lists.postgresql.org; Wed, 30 Sep 2020 11:25:14 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNaEP-0001Nz-KB for pgsql-hackers@lists.postgresql.org; Wed, 30 Sep 2020 11:25:12 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 6167C5C006A; Wed, 30 Sep 2020 07:25:07 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Wed, 30 Sep 2020 07:25:07 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ilmari.org; h= from:to:cc:subject:references:date:in-reply-to:message-id :mime-version:content-type; s=fm3; bh=uIt7XHDh1XJv44redLDU9d1D7F Glf0iT+XpGTKwIHYU=; b=Z6nrGgbDVX5n66ImLhLQ/oTB9e7J+XGnbSAYnaVNXB Vkx4XYJIvLF2tp3wu108R1xgsBHXW0txke0N4hZsHototlEshJefl4K8Nng03yLp 7IF60jVSiHVlqYk3kYwHmLoh/KDJl9j9HUyhRwF1Qzp6VePz9Ve3NCQbiItjgrKj h8dwkkC8Cf+L0v87jlVfbj5gV/rlLX+/FTIKxkIkRF2sETQkBIacdl2pgXQC4a6x hbaijsSPImQ3dkMA+7aL/wscTFEORQF12hotPOzwKwsGd8bJztpk811Wcl8xy5jw DdLowAS4QeMnZ9ryhSoHGZ9YX5vFLZKGfCA2VApSlOvg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; bh=uIt7XH Dh1XJv44redLDU9d1D7FGlf0iT+XpGTKwIHYU=; b=WQA8isYp8i6ZlQFmPwlT2f riWbvIcxvDF4wXrAto0ZNxK7uwyRoIY+Ip8W/UX4hhGfQ8MvaNrSfQ/yboMGGDuU xPwIrwhx7SK4ClutJsTgg4iqM+AaRjSq8ZRgFPuqcnXyE5P1L7pD9/gprzY2CHo7 2P6ix/rPrH36dk0VH3oK0d0U2484XGLAUu9zL6VkgHj0y5a03y8jmwOPklcXoZ5l IYFZ9DivOn/ad5CHNgB9OsFTga8C4/JNxy/CTlqMuTf2nkcy8ibcNQ/3yfZxlg7R ISCfZNSs75Yhfzm/INAf7giFDz/5xiSLAsn5vQd8PPZA+ISGmcIjuaW5T15W3zuw == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedrfedvgdefiecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdefhedmnecujfgurhephffvufhfffgjkfgfgggtsehmtderredtreej necuhfhrohhmpehilhhmrghrihesihhlmhgrrhhirdhorhhgucdlffgrghhfihhnnhcukf hlmhgrrhhiucforghnnhhsnohkvghrmdenucggtffrrghtthgvrhhnpeekkeffueekgfdu kedugfelledvgeejtefgleduhfehteeffeevgfeguedtffekhfenucfkphepgeeirdegfe drudehrddvhedvnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhf rhhomhepihhlmhgrrhhisehilhhmrghrihdrohhrgh X-ME-Proxy: Received: from wibble.ilmari.org (wibble.ilmari.org [46.43.15.252]) by mail.messagingengine.com (Postfix) with ESMTPA id B5B9D3064686; Wed, 30 Sep 2020 07:25:05 -0400 (EDT) From: ilmari@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) To: Michael Paquier Cc: =?utf-8?Q?J=C3=BCrgen?= Purtz , pgsql-hackers@lists.postgresql.org Subject: Re: [PATCH] Add section headings to index types doc References: <877dukhvzg.fsf@wibble.ilmari.org> <2268714.1596457003@sss.pgh.pa.us> <159706393772.790.11301900224459454375.pgcf@coridan.postgresql.org> <20200930063423.GG1996@paquier.xyz> Date: Wed, 30 Sep 2020 12:25:03 +0100 In-Reply-To: <20200930063423.GG1996@paquier.xyz> (Michael Paquier's message of "Wed, 30 Sep 2020 15:34:23 +0900") Message-ID: <87pn63ecc0.fsf@wibble.ilmari.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.1 (gnu/linux) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Michael Paquier writes: > On Mon, Aug 10, 2020 at 12:52:17PM +0000, Jürgen Purtz wrote: >> The new status of this patch is: Waiting on Author > > This has not been answered yet, so I have marked the patch as returned > with feedback. Updated patch attached, wich reformats the operator lists as requested by Jürgen, and skips the reindentation as suggested by Tom. The reindentation patch is attached separately, in case the committer decides they want it properly indented after all. - ilmari -- - Twitter seems more influential [than blogs] in the 'gets reported in the mainstream press' sense at least. - Matt McLeod - That'd be because the content of a tweet is easier to condense down to a mainstream media article. - Calle Dybedahl --=-=-= Content-Type: text/x-diff Content-Disposition: inline; filename=0001-Add-section-headers-to-index-types-doc.patch From 84522dc77afd1b8ce0bf111279302888d9d3edcb Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= Date: Fri, 31 Jul 2020 10:20:48 +0100 Subject: [PATCH] Add section headers to index types doc This makes it easier to compare the properties of different index types at a glance. In passing, make the index operator lists a single line each. --- doc/src/sgml/indices.sgml | 81 +++++++++++++++++++-------------------- 1 file changed, 39 insertions(+), 42 deletions(-) diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 671299ff05..f22253f4c3 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -122,6 +122,9 @@ B-tree indexes, which fit the most common situations. + + B-tree + index @@ -137,13 +140,9 @@ will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: - - < - <= - = - >= - > - + +<   <=   =   >=   > + Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with @@ -172,6 +171,10 @@ This is not always faster than a simple scan and sort, but it is often helpful. + + + + Hash @@ -191,6 +194,10 @@ CREATE INDEX name ON table USING HASH (column); + + + + GiST @@ -210,20 +217,9 @@ for several two-dimensional geometric data types, which support indexed queries using these operators: - - << - &< - &> - >> - <<| - &<| - |&> - |>> - @> - <@ - ~= - && - + +<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   && + (See for the meaning of these operators.) @@ -246,6 +242,10 @@ In , operators that can be used in this way are listed in the column Ordering Operators. + + + + SP-GiST @@ -264,14 +264,9 @@ for two-dimensional points, which support indexed queries using these operators: - - << - >> - ~= - <@ - <^ - >^ - + +<<   >>   ~=   <@   <^   >^ + (See for the meaning of these operators.) @@ -286,6 +281,10 @@ corresponding operator is specified in the Ordering Operators column in . + + + + GIN @@ -312,12 +311,9 @@ PostgreSQL includes a GIN operator class for arrays, which supports indexed queries using these operators: - - <@ - @> - = - && - + +<@   @>   =   && + (See for the meaning of these operators.) @@ -327,6 +323,10 @@ classes are available in the contrib collection or as separate projects. For more information see . + + + + BRIN @@ -348,18 +348,15 @@ values in the column for each block range. This supports indexed queries using these operators: - - < - <= - = - >= - > - + +<   <=   =   >=   > + The BRIN operator classes included in the standard distribution are documented in . For more information see . + -- 2.27.0 --=-=-= Content-Type: text/x-diff Content-Disposition: inline; filename=0002-Reindent-index-types-docs-after-previous-commit.patch From 2a75f535e60e89ed7e2276f4775395162f364e8c Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= Date: Tue, 1 Sep 2020 11:48:59 +0100 Subject: [PATCH 2/2] Reindent index types docs after previous commit The previous commit wrapped each index type descripiton in a `` tag, but avoided reidenting for ease of review. This commit reindents everything, except the `` and `` tags, which appear to need to be in column zero. --- doc/src/sgml/indices.sgml | 338 +++++++++++++++++++------------------- 1 file changed, 169 insertions(+), 169 deletions(-) diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index f22253f4c3..3e11d3917f 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -125,237 +125,237 @@ B-tree - - - index - B-tree - - - B-tree - index - - 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: + + + index + B-tree + + + B-tree + index + + 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: <   <=   =   >=   > - 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. - + 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. + - - 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 - 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. - + + 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 + 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. + - - 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. - + + 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. + Hash - - - index - hash - - - hash - index - - Hash indexes can only handle simple equality comparisons. - The query planner will consider using a hash index whenever an - indexed column is involved in a comparison using the - = operator. - The following command is used to create a hash index: + + + index + hash + + + hash + index + + Hash indexes can only handle simple equality comparisons. + The query planner will consider using a hash index whenever an + indexed column is involved in a comparison using the + = operator. + The following command is used to create a hash index: CREATE INDEX name ON table USING HASH (column); - + GiST - - - index - GiST - - - GiST - index - - GiST indexes are not a single kind of index, but rather an infrastructure - within which many different indexing strategies can be implemented. - Accordingly, the particular operators with which a GiST index can be - used vary depending on the indexing strategy (the operator - class). As an example, the standard distribution of - PostgreSQL includes GiST operator classes - for several two-dimensional geometric data types, which support indexed - queries using these operators: + + + index + GiST + + + GiST + index + + GiST indexes are not a single kind of index, but rather an infrastructure + within which many different indexing strategies can be implemented. + Accordingly, the particular operators with which a GiST index can be + used vary depending on the indexing strategy (the operator + class). As an example, the standard distribution of + PostgreSQL includes GiST operator classes + for several two-dimensional geometric data types, which support indexed + queries using these operators: <<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   && - (See for the meaning of - these operators.) - The GiST operator classes included in the standard distribution are - documented in . - Many other GiST operator - classes are available in the contrib collection or as separate - projects. For more information see . - + (See for the meaning of + these operators.) + The GiST operator classes included in the standard distribution are + documented in . + Many other GiST operator + classes are available in the contrib collection or as separate + projects. For more information see . + - - GiST indexes are also capable of optimizing nearest-neighbor - searches, such as + + GiST indexes are also capable of optimizing nearest-neighbor + searches, such as point '(101,456)' LIMIT 10; ]]> - which finds the ten places closest to a given target point. The ability - to do this is again dependent on the particular operator class being used. - In , operators that can be - used in this way are listed in the column Ordering Operators. - + which finds the ten places closest to a given target point. The ability + to do this is again dependent on the particular operator class being used. + In , operators that can be + used in this way are listed in the column Ordering Operators. + SP-GiST - - - index - SP-GiST - - - SP-GiST - index - - SP-GiST indexes, like GiST indexes, offer an infrastructure that supports - various kinds of searches. SP-GiST permits implementation of a wide range - of different non-balanced disk-based data structures, such as quadtrees, - k-d trees, and radix trees (tries). As an example, the standard distribution of - PostgreSQL includes SP-GiST operator classes - for two-dimensional points, which support indexed - queries using these operators: + + + index + SP-GiST + + + SP-GiST + index + + SP-GiST indexes, like GiST indexes, offer an infrastructure that supports + various kinds of searches. SP-GiST permits implementation of a wide range + of different non-balanced disk-based data structures, such as quadtrees, + k-d trees, and radix trees (tries). As an example, the standard distribution of + PostgreSQL includes SP-GiST operator classes + for two-dimensional points, which support indexed + queries using these operators: <<   >>   ~=   <@   <^   >^ - (See for the meaning of - these operators.) - The SP-GiST operator classes included in the standard distribution are - documented in . - For more information see . - + (See for the meaning of + these operators.) + The SP-GiST operator classes included in the standard distribution are + documented in . + For more information see . + - - Like GiST, SP-GiST supports nearest-neighbor searches. - For SP-GiST operator classes that support distance ordering, the - corresponding operator is specified in the Ordering Operators - column in . - + + Like GiST, SP-GiST supports nearest-neighbor searches. + For SP-GiST operator classes that support distance ordering, the + corresponding operator is specified in the Ordering Operators + column in . + GIN - - - index - GIN - - - GIN - index - - GIN indexes are inverted indexes which are appropriate for - data values that contain multiple component values, such as arrays. An - inverted index contains a separate entry for each component value, and - can efficiently handle queries that test for the presence of specific - component values. - + + + index + GIN + + + GIN + index + + GIN indexes are inverted indexes which are appropriate for + data values that contain multiple component values, such as arrays. An + inverted index contains a separate entry for each component value, and + can efficiently handle queries that test for the presence of specific + component values. + - - Like GiST and SP-GiST, GIN can support - many different user-defined indexing strategies, and the particular - operators with which a GIN index can be used vary depending on the - indexing strategy. - As an example, the standard distribution of - PostgreSQL includes a GIN operator class - for arrays, which supports indexed queries using these operators: + + Like GiST and SP-GiST, GIN can support + many different user-defined indexing strategies, and the particular + operators with which a GIN index can be used vary depending on the + indexing strategy. + As an example, the standard distribution of + PostgreSQL includes a GIN operator class + for arrays, which supports indexed queries using these operators: <@   @>   =   && - (See for the meaning of - these operators.) - The GIN operator classes included in the standard distribution are - documented in . - Many other GIN operator - classes are available in the contrib collection or as separate - projects. For more information see . - + (See for the meaning of + these operators.) + The GIN operator classes included in the standard distribution are + documented in . + Many other GIN operator + classes are available in the contrib collection or as separate + projects. For more information see . + BRIN - - - index - BRIN - - - BRIN - index - - BRIN indexes (a shorthand for Block Range INdexes) store summaries about - the values stored in consecutive physical block ranges of a table. - Like GiST, SP-GiST and GIN, - BRIN can support many different indexing strategies, - and the particular operators with which a BRIN index can be used - vary depending on the indexing strategy. - For data types that have a linear sort order, the indexed data - corresponds to the minimum and maximum values of the - values in the column for each block range. This supports indexed queries - using these operators: + + + index + BRIN + + + BRIN + index + + BRIN indexes (a shorthand for Block Range INdexes) store summaries about + the values stored in consecutive physical block ranges of a table. + Like GiST, SP-GiST and GIN, + BRIN can support many different indexing strategies, + and the particular operators with which a BRIN index can be used + vary depending on the indexing strategy. + For data types that have a linear sort order, the indexed data + corresponds to the minimum and maximum values of the + values in the column for each block range. This supports indexed queries + using these operators: <   <=   =   >=   > - The BRIN operator classes included in the standard distribution are - documented in . - For more information see . - + The BRIN operator classes included in the standard distribution are + documented in . + For more information see . + -- 2.27.0 --=-=-=--