Indexing on PQS contract payloads

Questions
1

Hi there. I would like to create some indexes for efficient queries on the active contract set using the participant query store. From what I understand, I would need to add an index to one of the tables created by scribe. Which table should I be using? I can see there is a _creates table and also various other tables for specific contracts/choice types.

2

Some guidance on creation of indexes for PQS can be found in the Participant Query Store User Guide — Daml SDK 2.8.3 documentation

The tables you will want to index will depend on your query access pattern, and where you are spotting bottlenecks, and likely specific to a particular contract and relevant fields for that contract.

1 Like
3

Hi Huw. I was in a similar situation myself recently.

There is a PSQL utility function that will help you precisely with this:

create_index_for_contract(name text, qname text, expression text, index_type text,
                                                      index_opclass text default '')

This allow you to create the indices by specifying the Daml template name directly.

1 Like
4

Thanks guys, I will take a look into these solutions