Обсуждение: Documentation on Hash and btree Indexes on jsonb
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
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