Indexes are not used in SELECT query by query planner

I have a SELECT Query, which joins three tables.
I have created indexes on the PKs and FKs of all the tables.
I made sure the indexes are created by using SHOW COLUMNS command.

As the query took longer than expected, I used EXPLAIN command with the SELECT query to see which indexes CRDB is using, I saw non.

Q1. Can CRDB find and use the indexes by itself or should I tell the query planner which index to use by myself?


I told the query planner which indexes to use by using @ after name of the tables and surprisingly the execution time increased almost 4 times of the original query without telling the query planner which indexes to use.

Q2. Any idea why that happened?

Thanks

In what testing I have done indexes are often used automatically. Can you do a
SHOW CREATE TABLE tablename;
on each of the tables, along with the SELECT query. Version of cockroach might help too in reproducing it.

1 Like

In general which primary keys or indexes are used depends on the WHERE or ON clauses of the SELECT queries you’re using. We cannot help you unless you share more about the structure of your queries and schema with us.

1 Like

Thanks, I did, following is an part of the result of running the command SHOW CREATE TABLE tablename for one of the tables:

CONSTRAINT “primary” PRIMARY KEY (“E_ID” ASC),
> |

| | UNIQUE INDEX “Employee_E_ID_key” (“E_ID” ASC),

Based on the lack of details, it’s likely your indexes are not created properly for your query. That’s all I can say without the full create tables of all 3 tables and the query…