When I am use 'order by Id DESC', query execution time increases more 25 seconds

Question

Hello I am use ClickHouse (so CH) version 21.3.20.1 I have Postgesql database by wegth more 70gb. I am creat table in CH

I create table in CH with next command:

CREATE TABLE test_una_analytics_service.messages
(Id UUID,
 ChatId UUID,
 User Nullable(String),
 Text Nullable(String),
 Type String, Timestamp DateTime64(6),
 From String, SessionId UUID,
 Card String, ConnectorId Nullable(String),
 ChannelId Nullable(String),
 ReplyTo Nullable(UUID),
 Data String,
 BehaviorType Nullable(String),
 CardId Nullable(UUID))
ENGINE = MaterializedPostgreSQL('localhost:5432', 'test_cit_router_api_service', 'messages' ,'postgres', '1')
PRIMARY KEY (Id)
ORDER BY (Timestamp, ChatId, Type, ChannelId);

and I get next tables: 2bb316a5-016c-45f4-ad94-45879a3e9bd8_nested messages

When I execute commands: 'show creat database "2bb316a5-016c-45f4-ad94-45879a3e9bd8_nested"' and 'show creat database messages', I view:

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │

 CREATE TABLE test_una_analytics_service.`2bb316a5-016c-45f4-ad94-45879a3e9bd8_nested`
(
    `Id` UUID,
    `ChatId` UUID,
    `User` Nullable(String),
    `Text` Nullable(String),
    `Type` String,
    `Timestamp` DateTime64(6),
    `From` String,
    `SessionId` UUID,
    `Card` String,
    `ConnectorId` Nullable(String),
    `ChannelId` Nullable(String),
    `ReplyTo` Nullable(UUID),
    `Data` String,
    `BehaviorType` Nullable(String),
    `CardId` Nullable(UUID),
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY Id
SETTINGS index_granularity = 8192

│ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │

CREATE TABLE test_una_analytics_service.messages
(
    `Id` UUID,
    `ChatId` UUID,
    `User` Nullable(String),
    `Text` Nullable(String),
    `Type` String,
    `Timestamp` DateTime64(6),
    `From` String,
    `SessionId` UUID,
    `Card` String,
    `ConnectorId` Nullable(String),
    `ChannelId` Nullable(String),
    `ReplyTo` Nullable(UUID),
    `Data` String,
    `BehaviorType` Nullable(String),
    `CardId` Nullable(UUID)
)
ENGINE = MaterializedPostgreSQL('192.168.20.216:5432', 'test_cit_router_api_service', 'messages', 'postgres', '1')
PRIMARY KEY Id
ORDER BY (Timestamp, ChatId, Type, ChannelId) 
```│
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

I exucte qwery:  
`SELECT *
FROM stage_una_analytics_service.messages
LIMIT 1`

Query id: ca2a8423-b0ba-42e4-87e8-e81c96c02d6e

┌─Id───────────────────────────────────┬─ChatId───────────────────────────────┬─
│ cbae836f-3ec3-4361-8000-000aae335435 │ 7230b230-9755-4ae2-9b63-190715ad5035 │ *************
└──────────────────────────────────────┴──────────────────────────────────────┴─

1 row in set. Elapsed: 0.708 sec.
It query execute quick.

But if I add order by:
`SELECT *
FROM stage_una_analytics_service.messages
ORDER BY Timestamp ASC
LIMIT 1`

Query id: 2fc5b74f-30a1-46c3-bba4-28b2ac7f09fa

┌─Id───────────────────────────────────┬─ChatId───────────────────────────────┬─User─
│ 65453b55-0789-4d2d-aad1-cd83a97fc7ce │ db51f127-46a2-4312-807d-1175912867c4 │ ᴺᵁᴸᴸ ***************
└──────────────────────────────────────┴──────────────────────────────────────┴──────

1 row in set. Elapsed: 28.296 sec. Processed 103.68 million rows, 34.37 GB (3.66 million rows/s., 1.21 GB/s.)

Time await query 28 sec and more..... 
If I change query and ORDER BY ASC change on the ORDER BY DESC, time execute query do not change.

And I have trouble this table 2bb316a5-016c-45f4-ad94-45879a3e9bd8_nested

When I add ORDER By, time execute query increases more 25 sec.

Question: Why, when I am use 'order by Id DESC', query execution time increases more 25 seconds.
Command show create table view, what I using sort index, and PRIMARY key. But query do not quckly execution.
What I must do for speed up query execution?

Education CkickHouse.

When I am use 'order by Id DESC', query execution time increases more 25 seconds

0 Answers0