为了更快的定位您的问题，请提供以下信息，谢谢
【详述】在使用limit分页操作时，limit 200w,50 耗时为5s。limit 2000w，50 耗时为50s。同时cpu与内存报警。
【背景】
【业务影响】查询速度慢，资源报警。
【是否存算分离】否
【StarRocks版本】3.2.3
【集群规模】1FE+1BE混部
【机器信息】22C/64G
【联系方式】社区群18-原色 邮箱：yuanse95@outlook.com
【附件】
explain costs:新建 文本文档.txt (3.6 KB)
表结构
CREATE TABLE judgment_documents (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT “自增主键”,
judgment_date date NOT NULL COMMENT “裁判日期”,
case_name varchar(65533) NOT NULL COMMENT “案件名称”,
case_number varchar(1024) NULL COMMENT “案号”,
original_link varchar(32) NULL COMMENT “原始链接”,
court varchar(512) NULL COMMENT “法院”,
jurisdiction varchar(1024) NULL COMMENT “所属地区”,
case_type varchar(1024) NULL COMMENT “案件类型”,
case_type_code varchar(1024) NULL COMMENT “案件类型编码”,
source varchar(1024) NULL COMMENT “来源”,
judicial_procedure varchar(1024) NULL COMMENT “审理程序”,
public_release_date date NULL COMMENT “公开日期”,
parties varchar(1024) NULL COMMENT “当事人”,
cause_of_action varchar(65533) NULL COMMENT “案由”,
legal_basis varchar(65533) NULL COMMENT “法律依据”,
full_text varchar(1048576) NULL COMMENT “全文”,
file_name varchar(1024) NULL COMMENT “来源文件名”
) ENGINE=OLAP
PRIMARY KEY(id, judgment_date)
COMMENT “数据”
PARTITION BY RANGE(judgment_date)
(PARTITION p19850120140101 VALUES [(“1985-01-01”), (“2014-01-01”)),
PARTITION p2014010120150101 VALUES [(“2014-01-01”), (“2015-01-01”)),
PARTITION p2015010120160101 VALUES [(“2015-01-01”), (“2016-01-01”)),
PARTITION p2016010120170101 VALUES [(“2016-01-01”), (“2017-01-01”)),
PARTITION p2017010120180101 VALUES [(“2017-01-01”), (“2018-01-01”)),
PARTITION p2018010120190101 VALUES [(“2018-01-01”), (“2019-01-01”)),
PARTITION p2019010120200101 VALUES [(“2019-01-01”), (“2020-01-01”)),
PARTITION p2020010120210101 VALUES [(“2020-01-01”), (“2021-01-01”)),
PARTITION p2021010120220101 VALUES [(“2021-01-01”), (“2022-01-01”)))
DISTRIBUTED BY HASH(judgment_date) BUCKETS 24
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
set pipeline_dop=1; 再跑下这个SQL看看多久能跑出来
先改写成这样，select * from judgment_documents where id in ( SELECT id FROM judgment_documents WHERE (judgment_date >= ‘2014-03-05’ AND judgment_date <= ‘2024-03-05’ AND 1 = 1) LIMIT 10000000,50 )，已知的bad case，当前还没人办去优化。
改写后是 228ms