# Filtering Shards during query with clustering column filter

**URL:** <https://community.cratedb.com/t/filtering-shards-during-query-with-clustering-column-filter/808>\
**Category:** CrateDB\
**Created:** [September 16, 2021, 4:52pm UTC](https://community.cratedb.com/t/filtering-shards-during-query-with-clustering-column-filter/808 "2021-09-16T16:52:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Francisco\_Javier\_Par](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/francisco_javier_par/32/391_2.png) [@Francisco\_Javier\_Par](https://community.cratedb.com/u/Francisco_Javier_Par)\
**Post date:** [September 16, 2021, 4:52pm UTC](https://community.cratedb.com/t/filtering-shards-during-query-with-clustering-column-filter/808/1 "2021-09-16T16:52:44Z")

</div>

Hello,

I could not find information in the documentation about whether the shards are filtered during a SELECT query if the filter of the query includes the CLUSTERING KEY.

For example, if a table like the following one

```auto
CREATE TABLE test.example (
   "c1" INTEGER,
   "c2" BIGINT,
   "c3" BIGINT,
   PRIMARY KEY ("c1", "c2")
)
CLUSTERED BY ("c1") INTO 6 SHARDS

```

is filtered with the following query

```auto
SELECT *
FROM test.example
WHERE c1 = 55

```

will the engine skip the shards that do not contain c1=55 or will it still search all the shards even if their distribution was based on this exact field?

Additionally, I am interesting in the equivalent question with the partition key instead. Will only the shards of one partition be searched in this case?

```auto
CREATE TABLE test.example (
   "c1" INTEGER,
   "c2" BIGINT,
   "c3" BIGINT,
   PRIMARY KEY ("c1", "c2")
)
PARTITIONED BY ("c1") 

```

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 17, 2021, 2:01pm UTC](https://community.cratedb.com/t/filtering-shards-during-query-with-clustering-column-filter/808/2 "2021-09-17T14:01:08Z")

</div>

> [@Francisco\_Javier\_Par](#):
>
> ```auto
> CREATE TABLE test.example (
> "c1" INTEGER,
> "c2" BIGINT,
> "c3" BIGINT,
> PRIMARY KEY ("c1", "c2")
> )
> PARTITIONED BY ("c1") 
> 
> ```

For partitions it is definitely taken into account. You can check that by running your queries with `EXPLAIN ANALYZE`. This shows which shards are queried.  
How it exactly work with only the routin\_column being set in `CLUSTERED BY` I would have to check.

Generally if you are interested in how CrateDB works I can recommend you the excellent articles from @mfussenegger

**How CrateDB plans and executes queries**  
[https://zignar.net/2021/05/15/how-cratedb-plans-and-executes-queries/](https://zignar.net/2021/05/15/how-cratedb-plans-and-executes-queries/)

**Distributed SELECT statement execution in CrateDB**  
[https://zignar.net/2021/05/20/distributed-select-statement-execution-in-cratedb/](https://zignar.net/2021/05/20/distributed-select-statement-execution-in-cratedb/)

**Query Then Fetch execution in CrateDB**  
[https://zignar.net/2021/05/28/query-then-fetch-execution-in-cratedb/](https://zignar.net/2021/05/28/query-then-fetch-execution-in-cratedb/)

**Group By query execution in CrateDB**  
[https://zignar.net/2021/06/04/group-by-query-execution-in-cratedb/](https://zignar.net/2021/06/04/group-by-query-execution-in-cratedb/)

---

<div class="post-metadata">

**Author:** ![mfussenegger](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mfussenegger/32/79_2.png) [@mfussenegger](https://community.cratedb.com/u/mfussenegger)\
**Post date:** [September 21, 2021, 2:55pm UTC](https://community.cratedb.com/t/filtering-shards-during-query-with-clustering-column-filter/808/3 "2021-09-21T14:55:05Z")

</div>

Turns out that it didn’t take the clustered by columns into consideration, but will in the future: [Optimize routing based on clustered by columns in where clause by mfussenegger · Pull Request #11753 · crate/crate · GitHub](https://github.com/crate/crate/pull/11753)