[mcdee]

I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue.

Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.

[malisper]

Using @> instead of ->> only causes the selectivity estimate of the predicate to be a different hard coded estimate. It doesn't fix the underlying problem of Postgres not keeping statistics on JSONB.

[mcdee]

True it doesn't solve the problem of not having statistics on the values, but it does bring the query response time down to the same order of magnitude as the non-JSON table.

[malisper]

> but it does bring the query response time down to the same order of magnitude as the non-JSON table.

In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.

[mcdee]

There are two separate issues. The lack of statistics is one thing, but the use of ->> instead of @> is another. Look at https://explain.depesz.com/s/zJiT Vs https://explain.depesz.com/s/ihwk for the difference.

[malisper]

Your queries are executing different plans. The first one is executing a nested loop join which filters out 1,246,035,384 intermediate rows. The second one is executing a index join which doesn't filter out any intermediate rows at all. This seems like it was caused either by the scientist_labs_pkey index not being there in the first trial or just random luck due to a difference in statistics.