You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "ntestoc3 (via GitHub)" <gi...@apache.org> on 2025/04/23 11:10:50 UTC
[I] [Bug] array类型多条件查询性能急剧下降 [doris]
ntestoc3 opened a new issue, #50342:
URL: https://github.com/apache/doris/issues/50342
### Search before asking
- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
Git : git://vm-241@39f9074cec769a10a0a93658b35d16ce59630e1e
Version : doris-3.0.4-rc02
BuildInfo : vm-241
BuildTime : Fri, 21 Feb 2025 16:28:34 1Z
### What's Wrong?
执行下面的查询语句包含2个条件执行会卡死，必须kill才行，否则影响整个集群的其他查询。
查询1的条件： WHERE (array_contains(cert_subject_root_domains, 'stackoverflow.com')) OR (array_contains(cert_subject_root_domains, 'cypress.com'))
只有一个查询条件的话1s就能返回，下面两个查询都能1s返回：
查询2的条件： WHERE (array_contains(cert_subject_root_domains, 'stackoverflow.com'))
查询3的条件： WHERE (array_contains(cert_subject_root_domains, 'cypress.com'))
去掉后面的cts查询， 只有base_data的查询2s就能返回，count或获取limit 10。
使用explain分析， 查询1和查询2只有下面的PREDICATES部分有区别
```
0:VOlapScanNode(4272) |
TABLE: fofa_data.fofa_data(fofa_data), PREAGGREGATION: ON |
PREDICATES: ((array_contains(cert_subject_root_domains[#52], 'cypress.com') AND (lastupdatetime[#2] > '2024-04-22 11:11:43')) AND (((is_fraud[#98] = FALSE) OR is_fraud[#98] IS NULL) AND (__DORIS_DELETE_SIGN__[#105] = 0))) |
partitions=1/1 (fofa_data) |
tablets=1000/1000, tabletList=1742801763726,1742801763729,1742801763732 ... |
cardinality=5832035224, avgRowSize=1287.6332, numNodes=2 |
pushAggOp=NONE |
final projections: asn_no[#66], asn_org[#67], cert_is_expired[#45], cert_is_match[#44], cert_is_valid[#43], cert_iss_sub_eq[#46], cert_issuer_cn_one[#53], cert_issuer_orgs[#55], cert_not_after[#57], cert_not_before[#58], cert_sn[#47], cert_subject_cn|
final project output tuple id: 1
```
执行profile对比两个查询， 查询1的OLAP_SCAN_OPERATOR占用时间特别长,profile结果文件：
[profiles.tgz](https://github.com/user-attachments/files/19866038/profiles.tgz)
```sql
WITH base_data AS (
SELECT
asn_no,
asn_org,
cert_is_expired,
cert_is_match,
cert_is_valid,
cert_iss_sub_eq,
cert_issuer_cn_one,
cert_issuer_orgs,
cert_not_after,
cert_not_before,
cert_sn,
cert_subject_cn,
cert_subject_org_one,
cert_subject_root_domains,
geoip_country_name,
ip,
lastupdatetime,
port,
protocol
FROM fofa_data
WHERE (array_contains(cert_subject_root_domains, 'stackoverflow.com')) OR (array_contains(cert_subject_root_domains, 'cypress.com')) AND lastupdatetime > DATE_SUB(NOW(), INTERVAL 1 YEAR) AND (is_fraud = false OR is_fraud IS NULL)
),
total_count_cte AS (
SELECT
COUNT(*) AS total_count
FROM base_data
),
unique_ip_count_cte AS (
SELECT
COUNT(DISTINCT ip) AS unique_ip_count
FROM base_data
WHERE ip IS NOT NULL
),
top_geoip_country_name AS (
SELECT
geoip_country_name,
COUNT(*) AS total_count
FROM base_data t1
WHERE geoip_country_name IS NOT NULL
GROUP BY geoip_country_name
ORDER BY total_count DESC
LIMIT 10
),
top_port AS (
SELECT
port,
COUNT(*) AS total_count
FROM base_data t1
WHERE port IS NOT NULL
GROUP BY port
ORDER BY total_count DESC
LIMIT 10
),
top_protocol AS (
SELECT
protocol,
COUNT(*) AS total_count
FROM base_data t1
WHERE protocol IS NOT NULL
GROUP BY protocol
ORDER BY total_count DESC
LIMIT 10
),
top_cert_subject_org_one AS (
SELECT
cert_subject_org_one,
COUNT(*) AS total_count
FROM base_data t1
WHERE cert_subject_org_one IS NOT NULL
GROUP BY cert_subject_org_one
ORDER BY total_count DESC
LIMIT 10
),
top_cert_sn AS (
SELECT
cert_sn,
COUNT(*) AS total_count,
any(cert_is_valid) as cert_is_valid,
any(cert_is_match) as cert_is_match,
any(cert_iss_sub_eq) as cert_iss_sub_eq,
any(cert_is_expired) as cert_is_expired,
any(CAST(CAST(cert_subject_root_domains AS JSON) AS STRING)) as cert_subject_root_domains,
any(cert_subject_org_one) as cert_subject_org_one,
any(cert_subject_cn) as cert_subject_cn,
any(CAST(CAST(cert_issuer_orgs AS JSON) AS STRING)) as cert_issuer_orgs,
any(cert_issuer_cn_one) as cert_issuer_cn_one,
any(cert_not_before) as cert_not_before,
any(cert_not_after) as cert_not_after
FROM base_data t1
WHERE cert_sn IS NOT NULL
GROUP BY cert_sn
ORDER BY total_count DESC
LIMIT 10
),
top_asn_no AS (
SELECT
asn_no,
COUNT(*) AS total_count,
any(asn_org) as asn_org
FROM base_data t1
WHERE asn_no IS NOT NULL
GROUP BY asn_no
ORDER BY total_count DESC
LIMIT 10
),
unique_geoip_country_name AS (
SELECT
COUNT(DISTINCT geoip_country_name) AS unique_count
FROM base_data
WHERE geoip_country_name IS NOT NULL
),
unique_port AS (
SELECT
COUNT(DISTINCT port) AS unique_count
FROM base_data
WHERE port IS NOT NULL
),
unique_protocol AS (
SELECT
COUNT(DISTINCT protocol) AS unique_count
FROM base_data
WHERE protocol IS NOT NULL
),
unique_cert_subject_org_one AS (
SELECT
COUNT(DISTINCT cert_subject_org_one) AS unique_count
FROM base_data
WHERE cert_subject_org_one IS NOT NULL
),
unique_cert_sn AS (
SELECT
COUNT(DISTINCT cert_sn) AS unique_count
FROM base_data
WHERE cert_sn IS NOT NULL
),
unique_asn_no AS (
SELECT
COUNT(DISTINCT asn_no) AS unique_count
FROM base_data
WHERE asn_no IS NOT NULL
)
SELECT
'country' AS field_name,
geoip_country_name AS field_value,
total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_geoip_country_name
UNION ALL
SELECT
'uniq' AS field_name,
'country' AS field_value,
unique_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_geoip_country_name
UNION ALL
SELECT
'port' AS field_name,
port AS field_value,
total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_port
UNION ALL
SELECT
'uniq' AS field_name,
'port' AS field_value,
unique_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_port
UNION ALL
SELECT
'protocol' AS field_name,
protocol AS field_value,
total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_protocol
UNION ALL
SELECT
'uniq' AS field_name,
'protocol' AS field_value,
unique_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_protocol
UNION ALL
SELECT
'cert.subject.org' AS field_name,
cert_subject_org_one AS field_value,
total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_cert_subject_org_one
UNION ALL
SELECT
'uniq' AS field_name,
'cert.subject.org' AS field_value,
unique_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_cert_subject_org_one
UNION ALL
SELECT
'cert.sn' AS field_name,
cert_sn AS field_value,
total_count AS count,
JSON_OBJECT(
'cert.sn', cert_sn,
'cert.is_expired', cert_is_expired,
'cert.is_match', cert_is_match,
'cert.is_valid', cert_is_valid,
'cert.is_equal', cert_iss_sub_eq,
'cert.issuer.cn', cert_issuer_cn_one,
'cert.issuer.org', CAST(cert_issuer_orgs AS JSONB),
'cert.not_after', cert_not_after,
'cert.not_before', cert_not_before,
'cert.subject.cn', cert_subject_cn,
'cert.subject.org', cert_subject_org_one,
'cert.domain', CAST(cert_subject_root_domains AS JSONB)
) AS detail,
NULL AS uniq_json
FROM top_cert_sn
UNION ALL
SELECT
'uniq' AS field_name,
'cert.sn' AS field_value,
unique_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_cert_sn
UNION ALL
SELECT
'asn' AS field_name,
asn_no AS field_value,
total_count AS count,
JSON_OBJECT(
'asn', asn_no,
'org', asn_org
) AS detail,
NULL AS uniq_json
FROM top_asn_no
UNION ALL
SELECT
'uniq' AS field_name,
'asn' AS field_value,
unique_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_asn_no
UNION ALL
SELECT
'total' AS field_name,
'count' AS field_value,
total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM total_count_cte
UNION ALL
SELECT
'uniq' AS field_name,
'ip' AS field_value,
unique_ip_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM unique_ip_count_cte
```
### What You Expected?
上面超长查询修改条件的查询时间应该相差不大，而不是单独查询1s，加一个OR条件直接导致执行时间超长，卡死集群。
### How to Reproduce?
_No response_
### Anything Else?
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] array类型多条件查询性能急剧下降 [doris]
Posted by "csun5285 (via GitHub)" <gi...@apache.org>.
csun5285 commented on issue #50342:
URL: https://github.com/apache/doris/issues/50342#issuecomment-3421548451
@ntestoc3 有两种可能的原因
1. ` (array_contains(cert_subject_root_domains, 'stackoverflow.com')) OR (array_contains(cert_subject_root_domains, 'cypress.com'))` 这个条件相比于单个条件的情况下执行过慢。
2. ` (array_contains(cert_subject_root_domains, 'stackoverflow.com')) OR (array_contains(cert_subject_root_domains, 'cypress.com'))` 这个条件下命中的行数相比于单个条件的情况下更多，导致后续的查询更慢。
`去掉查询1后面的cte查询部分， 只有base_data的查询2s就能返回，count或获取limit 10。` 这句话说的 base_data 的查询2s 是 select count() from table where 这种形式的话，就可以排除原因 1.
麻烦 @ntestoc3 提供 select count() from table where (array_contains(cert_subject_root_domains, 'stackoverflow.com')) OR (array_contains(cert_subject_root_domains, 'cypress.com')) 时的 count 和 单个条件下的 count，分别是多少。
以及相应的建表语句。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] array类型多条件查询性能急剧下降 [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #50342: [Bug] array类型多条件查询性能急剧下降
URL: https://github.com/apache/doris/issues/50342
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] array类型多条件查询性能急剧下降 [doris]
Posted by "csun5285 (via GitHub)" <gi...@apache.org>.
csun5285 commented on issue #50342:
URL: https://github.com/apache/doris/issues/50342#issuecomment-3424537634
定位到 array 类型的 read by rowids 应该批量读取
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] array类型多条件查询性能急剧下降 [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #50342:
URL: https://github.com/apache/doris/issues/50342#issuecomment-4277184954
We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org