 Try to improve fts for big datasets by stage table join
Following PR https://github.com/huggingface/datasets-server/pull/2633 and comment https://github.com/huggingface/datasets-server/pull/2633#issuecomment-2021117342 I would like to first try with a JOIN approach:

1. Get the rows that match the query criteria (score NOT NULL)
2.  JOIN with the `data` table to get the other columns 
 
This approach should be better than the current process, but it might still not work for big datasets since the "data" table does not have a PRIMARY KEY yet. See https://github.com/huggingface/datasets-server/blob/main/services/worker/src/worker/job_runners/split/duckdb_index.py#L58 we only define the sequence, but it is still a heap table (without indexes to improve the duckdb query plan). 
If this approach sounds good, it will be necessary to change `split-duckdb-index` to add a PRIMARY KEY at table creation, but it will affect the file size. For `wikimedia/wikipedia`, I tried adding the index, and it increased from 9GB to 13 GB. We have to put in a balance between space/performance. Otherwise, growing memory of search pods would also help.
 
 Try to improve fts for big datasets by stage table join: Following PR https://github.com/huggingface/datasets-server/pull/2633 and comment https://github.com/huggingface/datasets-server/pull/2633#issuecomment-2021117342 I would like to first try with a JOIN approach:

1. Get the rows that match the query criteria (score NOT NULL)
2.  JOIN with the `data` table to get the other columns 
 
This approach should be better than the current process, but it might still not work for big datasets since the "data" table does not have a PRIMARY KEY yet. See https://github.com/huggingface/datasets-server/blob/main/services/worker/src/worker/job_runners/split/duckdb_index.py#L58 we only define the sequence, but it is still a heap table (without indexes to improve the duckdb query plan). 
If this approach sounds good, it will be necessary to change `split-duckdb-index` to add a PRIMARY KEY at table creation, but it will affect the file size. For `wikimedia/wikipedia`, I tried adding the index, and it increased from 9GB to 13 GB. We have to put in a balance between space/performance. Otherwise, growing memory of search pods would also help.
 