[vog]

> It has no way of knowing, for example, that record ->> 'value_2' = 0 will be true about 50% of the time

Can't this be solved by introducing an expression index[1] for "record ->> 'value_2'"?

This would add a specialized index that will be used of all queries that have a filter like "WHERE record ->> 'value_2' = 0 AND ...".

[1] https://www.postgresql.org/docs/current/static/indexes-expre...

[drob]

The expression index will make it fast to retrieve the rows for which that predicate is true, but it won't help the planner know that this will be the case for 50% of rows, so I don't think it will change the join that the planner selects (which is the problem here).

In fact, this might make the query slower. If postgres thinks it is selecting a very small number of rows, it will prefer an index scan of some kind, but a full table scan will be faster if it's retrieving 1/8th of the table (at least, for small rows like these). So, you might get a slower row retrieval and the same explosively slow join.

[parenthephobia]

Postgres stores statistics for expression indexes, so it can know that the predicate is true for half the rows.

In the worked example, adding expression indices for the integer values of value_1, value_2, and value_3 makes the JSONB solution only marginally less efficient than the full-column solution. On my computer, ~300ms instead of ~200ms.

(This is Postgres 9.5)

[malisper]

A while ago I came across this thread[0] in which Tom Lane brings up the fact that statistics are kept on functional indexes. I can't remember why, but for some reason I couldn't get the planner to do what I specifically wanted. It may have been a weird detail about composite types.

Separately, the big downside I see with this approach is that it requires indexes on every field you would ever query by. If we were to create expression indexes on each field in the jsonb blob, that would effectively double the amount of space being used as well as dramatically increase the write cost.

[0] https://www.postgresql.org/message-id/6668.1351105908%40sss....