Documentation on Hash and btree Indexes on jsonb

Started by PG Bug reporting formabout 5 years ago2 messagesdocs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/datatype-json.html
Description:

Hello everyone,

we are planning to test postgres as a nosql database for streaming kafka
events (converted to jsonb) into it next week.

Therefore I am researching on index types. We would mostly be using queries
like message @> '{"fahrtnummer": "37458"}' or message ->> 'fahrtnummer' =
'37458' respectively. Where fahrtnummer always exists and is not an array.

The documentation on https://www.postgresql.org/docs/12/datatype-json.html
claims:
"jsonb also supports btree and hash indexes. These are usually useful only
if it's important to check equality of complete JSON documents."

According to some older information from 2016

https://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes/?ckattempt=1
https://stackoverflow.com/questions/36075918/postgresql-index-on-json
it seems to be a valid option to use hash or btree indexes in that case
however.
"As a result, in the choice of an index for JSONB type, if the index
searches a few predetermined attributes, BTREE is the best choice"

The quoted sentance from the postgres documentation would suggest, that this
option could be discarded. I would love to hear your feedback on this
matter.

Thank you!
Holger

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: PG Bug reporting form (#1)
Re: Documentation on Hash and btree Indexes on jsonb

On Sat, 2021-02-13 at 09:37 +0000, PG Doc comments form wrote:

The documentation on https://www.postgresql.org/docs/12/datatype-json.html
claims:
"jsonb also supports btree and hash indexes. These are usually useful only
if it's important to check equality of complete JSON documents."

According to some older information from 2016

https://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes/?ckattempt=1
https://stackoverflow.com/questions/36075918/postgresql-index-on-json
it seems to be a valid option to use hash or btree indexes in that case
however.
"As a result, in the choice of an index for JSONB type, if the index
searches a few predetermined attributes, BTREE is the best choice"

The quoted sentance from the postgres documentation would suggest, that this
option could be discarded. I would love to hear your feedback on this
matter.

The PostgreSQL documentation is talking about a B-tree index on the complete
JSON, while the article is talking about a B-tree index on an expression that
extracts one attribute of the JSON.

These are quite different things, so both are correct.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com