Larborator opened a new issue, #11511: URL: https://github.com/apache/doris/issues/11511
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Version 1.1.0-rc05 ### What's Wrong? When the prefix index is `datetime` or `date`, and the `where` condition is range query, the prefix index may be invalid. (seen from Doris UI QueryProfile) But the SQL execution time is the same. I guess it's a display problem. eg: CREATE TABLE `dwd_dy_log_dot_hi` ( `ht` DATETIME NULL, `action_code` CHAR(12) NULL, `dateline` BIGINT(20) NULL, `uid` BIGINT(20) NULL, `did` VARCHAR(40) NULL, `room_id` BIGINT(20) NULL, `ct_code` CHAR(2) NULL, `page_code` CHAR(3) NULL, `module_code` CHAR(2) NULL, `func_code` CHAR(3) NULL, `ip` VARCHAR(255) NULL, `occur_time` BIGINT(20) NULL, `event_id` BIGINT(20) NULL, `version` VARCHAR(255) NULL, `type` BIGINT(20) NULL, `duration` BIGINT(20) NULL, `pre_action_code` VARCHAR(20) NULL, `chan_code` VARCHAR(255) NULL, `url` VARCHAR(2000) NULL, `ref_url` VARCHAR(2000) NULL, `cate_id` BIGINT(20) NULL, `tag_id` BIGINT(20) NULL, `child_id` BIGINT(20) NULL, `pos` BIGINT(20) NULL, `ext` TEXT NULL, `session_id` BIGINT(20) NULL, `network_type` VARCHAR(255) NULL, `user_agent` VARCHAR(2000) NULL, `browser_name` VARCHAR(255) NULL, `browser_version` VARCHAR(255) NULL, `os_name` VARCHAR(255) NULL, `os_version` VARCHAR(255) NULL, `ct_pt` VARCHAR(10) NULL ) PARTITION BY RANGE(`ht`)() DISTRIBUTED BY HASH(`ht`, `action_code`, `dateline`) BUCKETS 64 PROPERTIES ( "replication_allocation" = "tag.location.group_dot: 2", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "HOUR", "dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" = "-750", "dynamic_partition.end" = "1", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 2", "dynamic_partition.buckets" = "64", "dynamic_partition.create_history_partition" = "true", "dynamic_partition.history_partition_num" = "-1", "dynamic_partition.hot_partition_num" = "0", "dynamic_partition.reserved_history_periods" = "NULL", "in_memory" = "false", "storage_format" = "V2", "compression" = "ZSTD" ) ; -- invalid prefix index sql is: SELECT count(did) as pv, count(distinct did) as uv, action_code, DATE_FORMAT(ht, 'yyyyMMdd') AS dt, uid FROM dwd_dy_log_dot_hi WHERE ht >= '2022-08-02 00:00:00' and ht <= '2022-08-02 23:00:00' AND action_code = '1001001' group by action_code, dt, uid; SegmentIterator: - BitmapIndexFilterTimer: 221.675us - BlockLoadTime: 2s455ms - BlockSeekCount: 1.385K (1385) - BlockSeekTime: 1s646ms - BlocksLoad: 618 - CachedPagesNum: 943 - CompressedBytesRead: 31.08 MB - DecompressorTimer: 370.755ms - IOTimer: 543.818ms - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 59 - NumSegmentTotal: 197 - RawRowsRead: 1.957531M (1957531) - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 147.162036M (147162036) - RowsKeyRangeFiltered: 0 - RowsStatsFiltered: 147.162036M (147162036) - RowsVectorPredFiltered: 1.832339M (1832339) - TotalPagesNum: 1.054K (1054) - UncompressedBytesRead: 58.63 MB - VectorPredEvalTime: 0ns -- valid prefix index sql is: SELECT count(did) as pv, count(distinct did) as uv, action_code, DATE_FORMAT(ht, 'yyyyMMdd') AS dt, uid FROM dwd_dy_log_dot_hi WHERE ht IN ( '2022-08-02 00:00:00', '2022-08-02 01:00:00', '2022-08-02 02:00:00', '2022-08-02 03:00:00', '2022-08-02 04:00:00', '2022-08-02 05:00:00', '2022-08-02 06:00:00', '2022-08-02 07:00:00', '2022-08-02 08:00:00', '2022-08-02 09:00:00', '2022-08-02 10:00:00', '2022-08-02 11:00:00', '2022-08-02 12:00:00', '2022-08-02 13:00:00', '2022-08-02 14:00:00', '2022-08-02 15:00:00', '2022-08-02 16:00:00', '2022-08-02 17:00:00', '2022-08-02 18:00:00', '2022-08-02 19:00:00', '2022-08-02 20:00:00', '2022-08-02 21:00:00', '2022-08-02 22:00:00', '2022-08-02 23:00:00' ) AND action_code = '1001001' group by action_code, dt, uid; SegmentIterator: - BitmapIndexFilterTimer: 110.744us - BlockLoadTime: 2s259ms - BlockSeekCount: 30.094K (30094) - BlockSeekTime: 2s22ms - BlocksLoad: 291 - CachedPagesNum: 1.243K (1243) - CompressedBytesRead: 15.45 MB - DecompressorTimer: 134.537ms - IOTimer: 1s381ms - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 66 - NumSegmentTotal: 211 - RawRowsRead: 68.151K (68151) - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 159.683881M (159683881) - RowsStatsFiltered: 157.657191M (157657191) - RowsVectorPredFiltered: 0 - TotalPagesNum: 1.338K (1338) - UncompressedBytesRead: 29.80 MB - VectorPredEvalTime: 0ns ### What You Expected? show the correct Query Profile or pptimize execution process. ### How to Reproduce? I can reproduce it by using the table structure above. ### Anything Else? The SQL execution time is the same. I guess it's a display problem. ### Are you willing to submit PR? - [X] 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-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
