[FEATURE] Possibility to specify secondary index for `Symbol`

[enricobenedos]

We started using QuestDb in our production environment to store also historical EOD market data.
About that we are experiencing slow queries when asking for "the first available data for that symbol":

SELECT * FROM eod_1d_raw 
WHERE identifier = 'BBG000BDTBL9'
LIMIT 0,1

so we tried to add the index for symbol column and now we are performing some tests (even if it seems that it doesn't solve the performance problem).

Shortly we recreate the eod_1d_raw table every day and now we need to create the associated index also.

Can you please add a way to specify we want the symbol index on table creation extension methods chain?

[nwoolmer]

This is unsupported by the ILP protocol currently. We could add extra steps to send queries over HTTP/PG to create/drop tables with specific schemas, which would require a little extra configuration from the user to support.

We will also be creating a revised protocol for the clients which can have a detailed header.

In the meantime, you will need to send the commands yourself. I used to do this with a timed script to drop and rebuild the database, which would run once a day. You can specify the index directly on the CREATE statement, or you can auto-create with ILP and then use ALTER.

Is the query slow because for some symbols, it has to scan too far back in the dataset? If so, we will soon be releasing materialised views with SAMPLE BY support. In the next iterations of this feature, we will be looking to support LATEST BY too, which would help to resolve this issue by another means.

[enricobenedos]

Oh, understood. Probably we fix it in the meanwhile adding also the Npgsql driver and manually executing the table creation instead of relying on this library.

I think you already know it but this cause a lot of boilerplate in our apps because we need every time two dependencies, two connection strings, two different implementations to inject every time.

For the performance case we aren't seeing big improvements. I don't know what's the right place to only spoke about our case with someone.

[nwoolmer]

Understood!

Maybe we can connect on the QuestDB Public Slack and go through your performance issue in more detail?

[enricobenedos]

Today we are encountering the same problem for the partitioning topic, we are not able to specify the partitioning using this implementation. By default it is set to day.