# Limited performance during query

**URL:** <https://community.cratedb.com/t/limited-performance-during-query/668>\
**Category:** CrateDB\
**Created:** [May 10, 2021, 1:40pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668 "2021-05-10T13:40:31Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 10, 2021, 1:40pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/1 "2021-05-10T13:40:31Z")

</div>

Hello,

I am testing performance on single node and noticed that queries are not utilizing all cpus from node.  
Node I am using is 32 cpu, 180G ram. I allocated 30G for heap.  
I have test table with about 123 milion rows.  
When I run below query it uses only one cpu and lasts for about 45 minutes.

```sql
SELECT order_id, count(*) from test_table group by order_id having count(*) = 3

```

when I run 32 smaller range queries like below in parallel I am getting results in 1 minute.

```sql
SELECT order_id, count(*) from test_table where order_id >= <min_id> and order_id < <max_id> group by order_id having count(*) = 3

```

Could you help me in this case?

Best regards,  
Kamil

---

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [May 10, 2021, 1:52pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/2 "2021-05-10T13:52:45Z")

</div>

Welcome Kamil,

can you please share the schema for your table `test_table` (SHOW CREATE TABLE)?

I suspect that you can improve the speed of your query if you would increase the number of shards of your table: [Sharding — CrateDB: Reference](https://crate.io/docs/crate/reference/en/4.5/general/ddl/sharding.html)

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 11, 2021, 3:45pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/3 "2021-05-11T15:45:33Z")

</div>

Hi,

thank you for reply. I forgot to mention that I have read sharding guide and set number of shards to 35 since we have 32 cores.

```auto
CREATE TABLE IF NOT EXISTS "test"."test_table" (
   "id" BIGINT,
   "added_date" TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   "called_from_queue" BOOLEAN DEFAULT NULL,
   "field_name" VARCHAR(255) DEFAULT NULL,
   "note" TEXT,
   "only_for_manager" BOOLEAN DEFAULT NULL,
   "order_id" BIGINT DEFAULT NULL,
   "queue_id" BIGINT DEFAULT NULL,
   "queue_item_id" BIGINT DEFAULT NULL,
   "user_id" BIGINT DEFAULT NULL,
   "__create_date" TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
   "__last_update_date" TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
   "uuid" VARCHAR(32) DEFAULT NULL,
   PRIMARY KEY ("id")
)
CLUSTERED BY ("id") INTO 35 SHARDS
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

```

---

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [May 12, 2021, 7:32am UTC](https://community.cratedb.com/t/limited-performance-during-query/668/4 "2021-05-12T07:32:45Z")

</div>

Hi @kamcio181,

thanks for the update.

Can you run an [`EXPLAIN ANALYZE`](https://crate.io/docs/crate/reference/en/4.5/sql/statements/explain.html?highlight=explain) for your query?

What kind of disk are you using on your CrateDB Node?

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 12, 2021, 1:30pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/5 "2021-05-12T13:30:04Z")

</div>

Hi,  
we are using 4 ssd nvme with raid10. You can find result of requested query below

> **Execution plan**
>
> ```json
> 
> {
> "Execute": {
> "Phases": {
> "0-collect": {
> "nodes": {
> "GDZZhPn8RSWPj7UMFcJE5Q": 2284793.327192
> }
> }
> },
> "Total": 2284801.445643,
> "GDZZhPn8RSWPj7UMFcJE5Q": {
> "QueryBreakdown": [
> {
> "QueryDescription": "*:*",
> "Time": 172.206001,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 161.861229,
> "match": 0,
> "next_doc_count": 3520551,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.668677,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 5.155507
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 185.778546,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 176.616145,
> "match": 0,
> "next_doc_count": 3526981,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.97355,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.661833
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 175.665283,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 164.981853,
> "match": 0,
> "next_doc_count": 3523506,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 1.799997,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 5.359886
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 168.945941,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 160.122916,
> "match": 0,
> "next_doc_count": 3521385,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 1.709572,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.592027
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 175.546478,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 167.101721,
> "match": 0,
> "next_doc_count": 3523780,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 34,
> "create_weight": 1.988218,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 2.932724
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 180.025146,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 170.609818,
> "match": 0,
> "next_doc_count": 3521473,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 1.928711,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.965103
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 176.388045,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 167.270801,
> "match": 0,
> "next_doc_count": 3526066,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 42,
> "create_weight": 1.949677,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.641458
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 183.906074,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 175.589898,
> "match": 0,
> "next_doc_count": 3526383,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 32,
> "create_weight": 1.833295,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 2.956465
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 187.333946,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 178.682996,
> "match": 0,
> "next_doc_count": 3524541,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.787126,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.339246
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 177.562455,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 169.114186,
> "match": 0,
> "next_doc_count": 3521581,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 38,
> "create_weight": 1.652437,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.274212
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 184.84258,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 175.062351,
> "match": 0,
> "next_doc_count": 3521546,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 34,
> "create_weight": 1.962042,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 4.296606
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 171.304633,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 162.486197,
> "match": 0,
> "next_doc_count": 3522522,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.925344,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.370533
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 178.684046,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 167.742435,
> "match": 0,
> "next_doc_count": 3524510,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 42,
> "create_weight": 1.742709,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 5.674349
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 189.239161,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 180.742796,
> "match": 0,
> "next_doc_count": 3524697,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 1.724638,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.246989
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 173.600452,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 165.130747,
> "match": 0,
> "next_doc_count": 3523101,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 32,
> "create_weight": 1.809175,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.137396
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 208.602528,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 199.655335,
> "match": 0,
> "next_doc_count": 3524511,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 44,
> "create_weight": 1.756836,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.665801
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 181.623638,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 172.598613,
> "match": 0,
> "next_doc_count": 3521632,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.914933,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.588423
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 222.621433,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 214.046279,
> "match": 0,
> "next_doc_count": 3518990,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 38,
> "create_weight": 1.677902,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.378223
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 200.272572,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 189.406681,
> "match": 0,
> "next_doc_count": 3525275,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 32,
> "create_weight": 1.942743,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 5.39784
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 178.946725,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 170.310094,
> "match": 0,
> "next_doc_count": 3525212,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 38,
> "create_weight": 1.63825,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.47313
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 177.338866,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 168.690358,
> "match": 0,
> "next_doc_count": 3525915,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.728944,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.393612
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 168.19582,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 159.837654,
> "match": 0,
> "next_doc_count": 3522963,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 1.815294,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.019868
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 189.574956,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 180.721205,
> "match": 0,
> "next_doc_count": 3520865,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 42,
> "create_weight": 1.687511,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.645332
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 177.80591,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 169.250172,
> "match": 0,
> "next_doc_count": 3522272,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 32,
> "create_weight": 1.712242,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.321191
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 196.784272,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 187.952184,
> "match": 0,
> "next_doc_count": 3524182,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 30,
> "create_weight": 1.954828,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.353047
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 177.863777,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 169.262618,
> "match": 0,
> "next_doc_count": 3522567,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 1.662129,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.416422
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 159.065024,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 150.789978,
> "match": 0,
> "next_doc_count": 3523914,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 30,
> "create_weight": 1.828514,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 2.922587
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 176.457865,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 167.494308,
> "match": 0,
> "next_doc_count": 3522978,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.756821,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.683721
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 169.890946,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 161.36187,
> "match": 0,
> "next_doc_count": 3525601,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 44,
> "create_weight": 1.855433,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.147997
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 179.95753,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 171.115696,
> "match": 0,
> "next_doc_count": 3523739,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 1.775181,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.542877
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 177.791032,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 168.922575,
> "match": 0,
> "next_doc_count": 3524596,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 46,
> "create_weight": 1.667235,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 3.676579
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 190.991435,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 182.834356,
> "match": 0,
> "next_doc_count": 3524923,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 32,
> "create_weight": 1.640636,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 2.991487
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 130.607159,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 126.7861,
> "match": 0,
> "next_doc_count": 3520841,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 36,
> "create_weight": 0.015895,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 0.284286
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 137.721373,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 133.839279,
> "match": 0,
> "next_doc_count": 3522631,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 40,
> "create_weight": 0.021026,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 0.338396
> },
> "QueryName": "MatchAllDocsQuery"
> },
> {
> "QueryDescription": "*:*",
> "Time": 139.141366,
> "BreakDown": {
> "match_count": 0,
> "shallow_advance_count": 0,
> "next_doc": 135.347255,
> "match": 0,
> "next_doc_count": 3522503,
> "score_count": 0,
> "compute_max_score_count": 0,
> "compute_max_score": 0,
> "advance": 0,
> "advance_count": 0,
> "score": 0,
> "build_scorer_count": 34,
> "create_weight": 0.012694,
> "shallow_advance": 0,
> "create_weight_count": 1,
> "build_scorer": 0.258879
> },
> "QueryName": "MatchAllDocsQuery"
> }
> ]
> }
> },
> "Plan": 0.929606,
> "Analyze": 1.744547
> }
> 
> ```

---

<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:** [May 12, 2021, 1:55pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/6 "2021-05-12T13:55:04Z")

</div>

Hi @kamcio181

Which CrateDB version are you using?  
Also where do you run this queries (client, Admin UI, etc.) ?  
How many distinct `order_id`s are there?

This seems very strange and not expected performance.

Similiar queries on bigger datasets return within seconds 🤔

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 12, 2021, 3:08pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/7 "2021-05-12T15:08:44Z")

</div>

We are using crate 4.5.1 I believe it is the latest one.  
Initially performance issue was noticed when running query using sqlalchemy + dialect from crate docs. But the explain query you have requested was run from Admin UI.  
there is 3,785,878 distinct order\_id, 109,952 of them are returned by query with having count(\*) = 3

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 14, 2021, 12:24pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/8 "2021-05-14T12:24:44Z")

</div>

Hi @proddata,

Is there any findings you can share? Or the issue is not easy to track?

---

<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:** [May 14, 2021, 12:44pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/9 "2021-05-14T12:44:34Z")

</div>

It its kinda hard to tell 😕

Could you run the following query to see how data is distributed across the shards

```sql
SELECT
MIN(size),MAX(size),AVG(size),
MIN(num_docs),MAX(num_docs),AVG(num_docs)
FROM sys.shards
where table_name = 'test_table' and primary
limit 100;

```

did you enable

```auto
bootstrap.memory_lock: true

```

in the `crate.yml`?

* * *

- have you tried dropping the `having count(*) = 3`?
- how many results are returned with the limited / full query?

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 14, 2021, 1:08pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/10 "2021-05-14T13:08:13Z")

</div>

| min(size) | max(size) | avg(size) | min(num\_docs) | max(num\_docs) | avg(num\_docs) |
| --- | --- | --- | --- | --- | --- |
| 835388219 | 843700972 | 839115134.3428571 | 3518971 | 3526963 | 3523373.8 |

Yes, I did enable

```auto
bootstrap.memory_lock: true

```

I will run query without having count(\*) = 3 and let you know about the results

---

<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:** [May 14, 2021, 1:39pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/11 "2021-05-14T13:39:25Z")

</div>

> [@kamcio181](#):
>
> ```auto
> SELECT order_id, count(*) from test_table group by order_id having count(*) = 3
> 
> ```

I just created a new table with

```auto
CREATE TABLE IF NOT EXISTS "test_table" (
   "id" BIGINT,
   order_id BIGINT DEFAULT NULL,
   PRIMARY KEY ("id")
)
CLUSTERED BY ("id") INTO 35 SHARDS;

```

and filled it with random data

```sql
INSERT INTO test_table SELECT col1 , CEIL(random()*(col1%4000000)) FROM Generate_series(1,123000000,1);

```

the cluster is a bit bigger, but the difference can’t quite be explained

```sql
SELECT order_id, count(*) from test_table2 group by order_id having count(*) = 3 limit 10000000;
-- SELECT OK, 116473 records returned (9.752 seconds)

```

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 14, 2021, 2:54pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/12 "2021-05-14T14:54:39Z")

</div>

I took the same steps as you and got results in about 30s, far better than 50 min.  
SELECT OK, 117052 records returned (29.239 seconds)

I will try to make the issue reproducible by generated data and provide step by step instruction

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 25, 2021, 1:27pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/13 "2021-05-25T13:27:28Z")

</div>

Hello @proddata,

I was able to prepare 5kk table which can be used for reproduction. You can find snapshot by following this url: [repro\_snapshot.tar.gz - Google Drive](https://drive.google.com/file/d/1ftgff6nzk2tRiWlFAneHI98BplkSMaNH/view?usp=sharing)  
Repro:

1. `DROP TABLE IF EXISTS doc.test5kk_025;`
2. `RESTORE SNAPSHOT test_repo.repro ALL;` - You probably need use name of own repository
3. Perform the query on that table. I got results within 13s.  
`select f, count(*) from doc.test5kk_025 group by f having count(*) = 3 limit 100000000;`  
`64782 records returned (11.612 seconds)`
4. Perform the query on table with values multiplied by 2. I got results within 2s.  
`select f * 2 as ff, count(*) from doc.test5kk_025 group by ff having count(*) = 3 limit 100000000;`  
`SELECT OK, 64782 records returned (2.211 seconds)`

---

<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:** [May 25, 2021, 1:54pm UTC](https://community.cratedb.com/t/limited-performance-during-query/668/14 "2021-05-25T13:54:03Z")

</div>

Thanks, we are looking into it 😉

---

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [May 26, 2021, 8:32am UTC](https://community.cratedb.com/t/limited-performance-during-query/668/15 "2021-05-26T08:32:02Z")

</div>

Hi @kamcio181,

short update: I’m able to reproduce this behaviour and did some further investigations. The core team will look into this. I’ll post an update once we understand this better.

**Update** : I opened [an issue on Github](https://github.com/crate/crate/issues/11413) for your report. You can subscribe to this issue to keep getting updated on the progress of this.

---

<div class="post-metadata">

**Author:** ![kamcio181](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/kamcio181/32/313_2.png) [@kamcio181](https://community.cratedb.com/u/kamcio181)\
**Post date:** [May 26, 2021, 10:26am UTC](https://community.cratedb.com/t/limited-performance-during-query/668/16 "2021-05-26T10:26:25Z")

</div>

@proddata, @jayeff thank you