Mongo but on Postgres and with strong consistency benefits

Re:Mongo but on Postgres and with strong consistency benefits by aden1ne on Monday July 08, 2024 @06:46AM (#40904235)

Had a similar experience. What killed it for me, is that no statistics can be gathered for JSONB columns. This in turn really messes with the query planner once you do something like `select a.* from a join b on a.id = b.a_id where b.my_jsonb_column ->> 'foo' = 'bar';`.

Given the lack of statistics, the query planner loves going for a nested loop rather than hash or merge join where those would appropriate, leading to abysmal performance.

There is an thread[0] on the PostgreSQL mailing list to add at least some statistics on JSONB column, but this has gone nowhere since 2022.

[0]: https://www.postgresql.org/message-id/flat/c9c4bd20-996c-100...

[ Parent | on HN ]

Re:Mongo but on Postgres and with strong consistency benefits by jeffffff on Wednesday July 10, 2024 @10:24AM (#40927249)

it's kind of dumb that postgres uses a nested loop join instead of a hash join there. hash join almost always has the best worst-case behavior, and without stats it should be the default choice.

[ Parent | on HN ]