Limited performance during query

CrateDB
1

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.

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.

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

2

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

3

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.

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'
)
4

Hi @kamcio181,

thanks for the update.

Can you run an EXPLAIN ANALYZE for your query?

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

5

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

Execution plan 

    {
      "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
    }
6

Hi @kamcio181

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

This seems very strange and not expected performance.

Similiar queries on bigger datasets return within seconds :thinking:

7

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

8

Hi @proddata,

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

9

It its kinda hard to tell :confused:

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

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

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?
10
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

bootstrap.memory_lock: true

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

11

I just created a new table with

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

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

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

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

2 Likes
13

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
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)
14

Thanks, we are looking into it :wink:

15

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 for your report. You can subscribe to this issue to keep getting updated on the progress of this.

1 Like
16

@proddata, @jayeff thank you

1 Like