Adding more indices won't improve query performance?

sql

(Derek Zhang) #1

Hi,

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.

Thanks,
Derek


(Ron Arévalo) #2

Hey Derek,

Adding indexes does cause a slow down in performance on writes/reads to your PRIMARY KEY. The second index you create has the largest impact, but additional indexes only cause negligible impact on performance. However, each query can only use a single index, Cockroachdb selects the index it calculates will scan the least amount of rows, you can read more about index selection here.

You can also force index selection by using the Force Index Selection notation, an example of how to do this is as follows:

SELECT name, balance
FROM accounts@accounts_name_idx
WHERE name = 'Edna Barath';

The @ followed by the index name is what allows you to force select an index.

You can read more about that here.

If you’d like to share your explain statements as well as your DDL we could take a look at this more closely.

Let us know!

Thanks,

Ron