5 ms·
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 usuall
by mcdee 10y ago
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 10y agoUsing @> 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 10y agoTrue 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 10y ago> 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 10y agoThere 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 https://explain.depesz.com/s/zJiT Vs https://explain.depesz.com/s/ihwk https://explain.depesz.com/s/ihwk for the difference.
- malisper 10y agoYour 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.