we are using PostgreSQL and we have a table we are using JSONB due to the unstructured nature of the data. We are integrating with a ton of different CRMs, so a part of the records data is pretty different from client to client. The schema is something like this:
CREATE TABLE records (
"client_id" INTEGER NOT NULL,
"fields" jsonb NOT NULL
)
For every client_id
, the fields
has a set of given keys in the JSON. So, we can something like:
client_id=1
: thousands of rows with json with the same keys (but different values).client_id=...
: thousands of rows with json with the same keys (but different values).client_id=n
: thousands of rows with json with the same keys (but different values).
Right now we have two different indexes:
- One on
client_id
- Another on
fields
Due to the fact that once you know the client_id, you also know the keys in the fields json, we were wondering if maybe the associated GIN index to fields
is more efficient if we create it as a multi column index on both (client_id
, fields
). Why do we think this? Because maybe postgres only takes into consideration the shape of those json for a given client id in the first column of the multi-colum index, instead of considering the whole column in the whole table.
We still have not benchmarked this, but we are curious if PostgreSQL takes things like this into consideration when creating multi-column indexes.
Note: it seems at least postgres, by default, do not create cross-column stats on multi column indexes (source)
client_id
are there? MIght be worthwhile creating filtered indexes for each.