0

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)

4
  • How many different client_id are there? MIght be worthwhile creating filtered indexes for each. Commented Sep 4 at 10:52
  • We were thinking about that. Not sure how much the business will grow, hopefully a lot, that's why we discarded that option. Right now we have around 100 clients, but hopefully we will have hundreds more. Commented Sep 4 at 11:10
  • What query were you hoping to improve?
    – jjanes
    Commented Sep 5 at 1:02
  • It's a "group of queries". Queries like: select * from records where client_id=123 and foobar. Where foobar is just checking the content of the json (either exact match of a value, range filter of a numeric value, etc). Commented Sep 5 at 7:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.