Re:When to Avoid JSONB in a PostgreSQL Schema
by mcdee on Thursday September 01, 2016 @05:51PM
(#12409174)
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.
[ Parent
| on HN ]
Re:When to Avoid JSONB in a PostgreSQL Schema
by malisper on Thursday September 01, 2016 @06:05PM
(#12409240)
> 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.
Re:When to Avoid JSONB in a PostgreSQL Schema
by mcdee on Thursday September 01, 2016 @06:14PM
(#12409276)
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.
Re:When to Avoid JSONB in a PostgreSQL Schema
by malisper on Thursday September 01, 2016 @06:30PM
(#12409355)
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.