mysql> EXPLAIN select count(*),

api

from tracing_normal group by

api

order by 1 desc limit 10\G *************************** 1. row *************************** plan_type: logical_plan plan: Sort: count(*) DESC NULLS FIRST, fetch=10 Projection: count(*), tracing_normal.api Aggregate: groupBy=[[tracing_normal.api]], aggr=[[__count_merge(__count_state(tracing_normal.ts)) AS count(*)]] MergeScan [is_placeholder=false, remote_input=[ Aggregate: groupBy=[[tracing_normal.api]], aggr=[[__count_state(tracing_normal.ts)]] TableScan: tracing_normal ]] *************************** 2. row *************************** plan_type: physical_plan plan: SortPreservingMergeExec: [count(*)@0 DESC], fetch=10 SortExec: TopK(fetch=10), expr=[count(*)@0 DESC], preserve_partitioning=[true] ProjectionExec: expr=[count()@1 as count(), api@0 as api] AggregateExec: mode=FinalPartitioned, gby=[api@0 as api], aggr=[count(*)] CoalesceBatchesExec: target_batch_size=8192 RepartitionExec: partitioning=Hash([api@0], 32), input_partitions=32 AggregateExec: mode=Partial, gby=[api@0 as api], aggr=[count(*)] MergeScanExec: peers=[4569845202944(1064, 0), 4569845202945(1064, 1), 4569845202946(1064, 2), 4569845202947(1064, 3), 4569845202948(1064, 4), 4569845202949(1064, 5), 4569845202950(1064, 6), 4569845202951(1064, 7), 4569845202952(1064, 8), 4569845202953(1064, 9), 4569845202954(1064, 10), 4569845202955(1064, 11), 4569845202956(1064, 12), 4569845202957(1064, 13), 4569845202958(1064, 14), 4569845202959(1064, 15), 4569845202960(1064, 16), 4569845202961(1064, 17), 4569845202962(1064, 18), 4569845202963(1064, 19), 4569845202964(1064, 20), 4569845202965(1064, 21), 4569845202966(1064, 22), 4569845202967(1064, 23), 4569845202968(1064, 24), 4569845202969(1064, 25), 4569845202970(1064, 26), 4569845202971(1064, 27), 4569845202972(1064, 28), 4569845202973(1064, 29), 4569845202974(1064, 30), 4569845202975(1064, 31), 4569845202976(1064, 32), 4569845202977(1064, 33), 4569845202978(1064, 34), 4569845202979(1064, 35), 4569845202980(1064, 36), 4569845202981(1064, 37), 4569845202982(1064, 38), 4569845202983(1064, 39), 4569845202984(1064, 40), 4569845202985(1064, 41), 4569845202986(1064, 42), 4569845202987(1064, 43), 4569845202988(1064, 44), 4569845202989(1064, 45), 4569845202990(1064, 46), 4569845202991(1064, 47), 4569845202992(1064, 48), 4569845202993(1064, 49), 4569845202994(1064, 50), 4569845202995(1064, 51), 4569845202996(1064, 52), 4569845202997(1064, 53), 4569845202998(1064, 54), 4569845202999(1064, 55), 4569845203000(1064, 56), 4569845203001(1064, 57), 4569845203002(1064, 58), 4569845203003(1064, 59), 4569845203004(1064, 60), 4569845203005(1064, 61), 4569845203006(1064, 62), 4569845203007(1064, 63), 4569845203008(1064, 64), 4569845203009(1064, 65), 4569845203010(1064, 66), 4569845203011(1064, 67), 4569845203012(1064, 68), 4569845203013(1064, 69), 4569845203014(1064, 70), 4569845203015(1064, 71), 4569845203016(1064, 72), 4569845203017(1064, 73), 4569845203018(1064, 74), 4569845203019(1064, 75), 4569845203020(1064, 76), 4569845203021(1064, 77), 4569845203022(1064, 78), 4569845203023(1064, 79), 4569845203024(1064, 80), 4569845203025(1064, 81), 4569845203026(1064, 82), 4569845203027(1064, 83), 4569845203028(1064, 84), 4569845203029(1064, 85), 4569845203030(1064, 86), 4569845203031(1064, 87), 4569845203032(1064, 88), 4569845203033(1064, 89), 4569845203034(1064, 90), 4569845203035(1064, 91), 4569845203036(1064, 92), 4569845203037(1064, 93), 4569845203038(1064, 94), 4569845203039(1064, 95), 4569845203040(1064, 96), 4569845203041(1064, 97), 4569845203042(1064, 98), 4569845203043(1064, 99), 4569845203044(1064, 100), 4569845203045(1064, 101), 4569845203046(1064, 102), 4569845203047(1064, 103), 4569845203048(1064, 104), 4569845203049(1064, 105), 4569845203050(1064, 106), 4569845203051(1064, 107), 4569845203052(1064, 108), 4569845203053(1064, 109), 4569845203054(1064, 110), 4569845203055(1064, 111), 4569845203056(1064, 112), 4569845203057(1064, 113), 4569845203058(1064, 114), 4569845203059(1064, 115), 4569845203060(1064, 116), 4569845203061(1064, 117), 4569845203062(1064, 118), 4569845203063(1064, 119), 4569845203064(1064, 120), 4569845203065(1064, 121), 4569845203066(1064, 122), 4569845203067(1064, 123), 4569845203068(1064, 124), 4569845203069(1064, 125), 4569845203070(1064, 126), 4569845203071(1064, 127), ] 2 rows in set (0.01 sec) @Ning

Hi, could you please use

explain analyze

or

explain analyze verbose

instead of just

explain

? By the way, are you using a cluster? What's your db version? How many nodes do you have? Partitioning may not always improve query performance, especially in some old versions.

The version of greptime is 0.16.0, running in standalone mode. Greptime, clickhouse, and Apache Druid all run on servers with the same configuration.

How many

serviceName

do you have? Could you please also send us the result of

EXPLAIN ANALYZE VERBOSE

for the same query? It might be large. In standalone mode, I’m not sure if it is suitable to have so many partitions for the same table.

In standalone mode, I think the partitions are unnecessary.

result.txt

In standalone mode, having so many shards only increases file fragmentation, resulting in a large number of small files, which can theoretically impact scan performance. I recommend not configuring partitions in standalone mode.

Thanks for the reminder, I'll try again

the write performance has deteriorated significantly

This is expected. We are improving this part. Could you share the

EXPLAIN ANALYZE VERBOSE

result again?

verbose090118.txt

@zyy17 Maybe we can reproduce this in our environment.

It seems the bottleneck is the scan cost. Noticed that the scan in partition

0

and

2

is

6.645933324s

and

4.829269549s

. @zhiqiang666 deng, it will be very helpful if you can help us provide the disk utilization. For example, you can run

iostat -x 1

at the same time as you execute the above query. Then you can observe some output metrics of

iostat

. The information will help you confirm the status of the local disk. Also, I will reproduce your scenario based on your provided information.

image.png