Postgres State Store - Support GIN indexes in Query API

Describe the feature

Certain databases like CosmosDB automatically index every key of a document, and benefit from improved query times on larger tables because of it

It would be nice if we could take advantage of the similar GIN index in Postgres.

From what I can tell, it's just a matter of exposing a toggle and a slightly different translateFieldToFilter function.

The current syntax for queries generated something like

SELECT * FROM public.state WHERE value->>'somefield' = 'somevalue'

Taking advantage of a GIN index is as simple as

SELECT * FROM public.state WHERE value @> '{"somefield": "somevalue"}';

I can contribute to this, but looking for some feedback on whether we have already considered something like this, and if no is it a good idea? Seems like a simple enough change with a big enough payday to be worthwhile. In some local testing I saw upwards of 7000x better performance on the above queries and a table with 10M record.

Release Note

Enable it on the State Store component, it would be nice if we could create the GIN index as well while creating the table if the setting is enabled.

RELEASE NOTE:

Start by locating the Postgres State Store Query API and the translateFieldToFilter function mentioned in the issue, then inspect how the state table is created. Check how a setting could control GIN index creation and query translation, and identify the existing tests for these paths. Done means the setting enables the index and corresponding @> queries without changing the default behavior.