I have a 1 million records table “test-1m” which has the primary key “id”, indexed columns “column1” and “column2”, and unindexed column “column3”, “column4” and “column5”.
To compare the performance with more indices, I created another 1 million records table “test-1m-all-indexed” which has the same columns but with them all indexed.
I executed the following query:
select * from “test-1m” where column2>100 and column4>10 and column5>1 limit 10;
select * from “test-1m-all-indexed” where column2>100 and column4>10 and column5>1 limit 10;
However, the second query is even a little slower than the first. Since the second table has more indices, I would expect the second query faster.
I used the EXPLAIN to figure out what happened. Looks the second query index-joined the primary key with column5 while the first query index-joined the primary key with column2.
So I am eager to ask below questions:
No matter how many indices I set, why Cockroach DB only pick up one to join with primary key? Is there a way to utilize other indices to boost query performance?
In the second query, why it picked up column5 rather than column2 to join with primary key? What is the mechanism behind the index picking? Is there a way to forcefully pick an indexed column by the user? I can hardly find an answer from a Google search so I asked here.