Query store SQL schema details for JSON API

I’m reading about the Query Store and was wondering about the schema that contracts were stored in.

Specifically:

  1. I am wondering about the efficiency of query language queries: how does a Daml schema translate to a Postgres schema and what SQL indices/keys are added to it?
  2. Follow up to 1, if we’re doing an equality or comparison query on an attribute that is not the key (in the Daml sense of “key”) of the contract, is it possible to query for that with the JSON API?
    a. If so, is that query on an indexed column in the query store?

We index the [surrogate] template ID, contract ID, and contract key (as in the one you define with key in Daml) columns.

Yes. In fact, you use entirely different endpoints for key lookup.

No. As noted here, we have not found any combination of use case and general purpose index that actually meaningfully improves things beyond what the tpid index does, in the way that the payload index on Oracle does. We would probably start here if we were to reconsider this, but as suggested in both of these issues, there is no obviously good strategy.

Thanks for the quick and detailed reply, Stephen!

Yes. In fact, you use entirely different endpoints for key lookup.

I see! I got confused because that page mentioned “keys” (e.g., “Match records having at least all the (potentially nested) keys expressed in the query.”) but wasn’t quite sure if that referred to a Daml key. I guess it refers to “key” in the sense of a JSON key?

beyond what the tpid index does, in the way that the payload index on Oracle does

I am unfamiliar with what the tpid index is. Would you mind pointing me in the right direction to learn more?

1 Like

Indeed.

As this regards internal implementation details, the only reliable source is the implementation.