I’m trying to run some benchmarks against a randomly - filled cockroach db, with just one table.
My table has a text column, named id, which for now just contains the values “1”… “10000”, another column named accountid (a string as well), and a few more timestamp / date / text colums
I have 2 clusters - one running locally with 3 dockers (following the instructions you posted), and another one runs in a similar way, on AWS cloud.
In both cases, queries like
select * from table take 4-5 seconds until they return the first result (my code is written in GO, using the pq driver). Running the query from one of the nodes directly (using your sql client) is just a bit faster, reducing the query to 3-4 seconds.
If i run more specific queries, such as
select id from table where accountid = 'myaccount' - it performs better, but only for cases where the index covers the columns im asking for. In all the cases my query included columns not covered by the particular index the db has used, performance was slow again.
Am i missing something very basic? This DB is supposed to be used as a storage for millions of rows, with a basic logical partition by the accountid column. The queries can be based on different permutations of the columns, therefor im not sure it’ll make sense to try and build specific indices for all the cases.
Thanks for your help!