pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedDocumentation on Hash and btree Indexes on jsonb
2+ messages / 2 participants
[nested] [flat]
* Documentation on Hash and btree Indexes on jsonb
@ 2021-02-13 09:37 PG Doc comments form <noreply@postgresql.org>
2021-02-15 12:53 ` Re: Documentation on Hash and btree Indexes on jsonb Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2021-02-13 09:37 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: holger.vornholt@deutschebahn.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Documentation on Hash and btree Indexes on jsonb
2021-02-13 09:37 Documentation on Hash and btree Indexes on jsonb PG Doc comments form <noreply@postgresql.org>
@ 2021-02-15 12:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2021-02-15 12:53 UTC (permalink / raw)
To: holger.vornholt@deutschebahn.com; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-02-15 12:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-13 09:37 Documentation on Hash and btree Indexes on jsonb PG Doc comments form <noreply@postgresql.org>
2021-02-15 12:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox