profile中BitmapIndexFilter不为了0，BitmapIndexFilterRows为0，是命中了索引吗？

💬 StarRocks 用户问答 性能相关
#1

问题：profile中BitmapIndexFilter不为了0，BitmapIndexFilterRows为0，是命中了索引吗？如果命中了索引，为什么BitmapIndexFilterRows为0
image

– dwh_tmp.tem_orders0 definition

CREATE TABLE tem_orders0 (
order_id bigint(20) NOT NULL COMMENT “”,
dt datetime NOT NULL COMMENT “”,
cnt int(11) NOT NULL COMMENT “”,
INDEX idx_dt1 (dt) USING BITMAP COMMENT ‘test’,
INDEX idx_2 (order_id) USING BITMAP COMMENT ‘test’
) ENGINE=OLAP
DUPLICATE KEY(order_id)
COMMENT “OLAP”
DISTRIBUTED BY HASH(order_id) BUCKETS 3
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);

profile :
Query:
Summary:
- Query ID: 3886392a-7156-11ed-8954-5254001a5302
- Start Time: 2022-12-01 16:57:44
- End Time: 2022-12-01 16:57:44
- Total: 4ms
- Query Type: Query
- Query State: EOF
- StarRocks Version: 2.2.2
- User: default_cluster:dwh_rw
- Default Db: default_cluster:dwh_dw
- Sql Statement: /* ApplicationName=DBeaver 22.1.4 - SQLEditor <script-15.sql> /
select * from dwh_tmp.tem_orders0 where dt >= ‘2022-10-02’
LIMIT 0, 200
- QueryCpuCost: 0
- QueryMemCost: 0.000
Execution Profile 3886392a-7156-11ed-8954-5254001a5302:(Active: 3.680ms[3680142ns], % non-child: 100.00%)
Fragment 0:
Instance 3886392a-7156-11ed-8954-5254001a5306 (host=TNetworkAddress(hostname:10.3.161.9, port:9060)):(Active: 1.696ms[1696928ns], % non-child: 1.21%)
- AverageThreadTokens: 0.00
- MemoryLimit: 24.00 GB
- PeakMemoryUsage: 1.27 KB
- RowsProduced: 3
DataBufferSender (dst_fragment_instance_id=3886392a-7156-11ed-8954-5254001a5306):
- AppendChunkTime: 6.162us
- ResultRendTime: 21.229us
- TupleConvertTime: 2.895us
- NumSentRows: 3
EXCHANGE_NODE (id=1):(Active: 1.652ms[1652457ns], % non-child: 44.90%)
- BytesPassThrough: 0.00
- BytesReceived: 100.00 B
- DecompressChunkTime: 0ns
- DeserializeChunkTime: 10.791us
- PeakMemoryUsage: 0.00
- ReceiverProcessTotalTime: 19.6us
- RequestReceived: 2
- RowsReturned: 3
- RowsReturnedRate: 1.815K /sec
- SenderTotalTime: 18.786us
- SenderWaitLockTime: 240ns
Fragment 1:
Instance 3886392a-7156-11ed-8954-5254001a5305 (host=TNetworkAddress(hostname:10.3.161.3, port:9060)):(Active: 1.91ms[1091833ns], % non-child: 0.00%)
- AverageThreadTokens: 0.00
- MemoryLimit: 24.00 GB
- PeakMemoryUsage: 0.00
- RowsProduced: 2
DataStreamSender (dst_id=1, dst_fragments=[3886392a715611ed-89545254001a5306]):(Active: 227.257us[227257ns], % non-child: 6.18%)
- PartType: UNPARTITIONED
- BytesSent: 60.00 B
- CompressTime: 3.396us
- IgnoreRows: 0
- OverallThroughput: 257.830078125 KB/sec
- SendRequestTime: 19.587us
- SerializeChunkTime: 4.528us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 60.00 B
- WaitResponseTime: 174.428us
OLAP_SCAN_NODE (id=0):(Active: 882.970us[882970ns], % non-child: 3.38%)
- Table: tem_orders0
- Rollup: tem_orders0
- Predicates: 2: dt >= ‘2022-10-02 00:00:00’
- BytesRead: 40.00 B
- IOTaskCount : 1
- NumDiskAccess: 0
- PeakMemoryUsage: 0.00
- RowsRead: 2
- RowsReturned: 2
- RowsReturnedRate: 2.265K /sec
- ScanConcurrency : 1
- ScanTime: 762.544us
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 758.505us[758505ns], % non-child: 20.61%)
- CachedPagesNum: 0
- CompressedBytesRead: 480.00 B
- CreateSegmentIter: 17.202us
- IOTime: 4.120us
- LateMaterialize: 17.693us
- PushdownPredicates: 1
- RawRowsRead: 2
- ReadPagesNum: 6
- SegmentInit: 709.224us
- BitmapIndexFilter: 634.323us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- SegmentZoneMapFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 13.686us
- BlockFetch: 2.124us
- BlockFetchCount: 1
- BlockSeek: 9.377us
- BlockSeekCount: 1
- ChunkCopy: 50ns
- DecompressT: 0ns
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 470ns
- PredFilterRows: 0
- RowsetsReadCount: 2
- SegmentsReadCount: 1
- TotalColumnsDataPageCount: 3
- UncompressedBytesRead: 330.00 B
Instance 3886392a-7156-11ed-8954-5254001a5304 (host=TNetworkAddress(hostname:10.3.161.15, port:9060)):(Active: 927.949us[927949ns], % non-child: 0.00%)
- AverageThreadTokens: 0.00
- MemoryLimit: 24.00 GB
- PeakMemoryUsage: 0.00
- RowsProduced: 1
DataStreamSender (dst_id=1, dst_fragments=[3886392a715611ed-89545254001a5306]):(Active: 205.345us[205345ns], % non-child: 5.58%)
- PartType: UNPARTITIONED
- BytesSent: 40.00 B
- CompressTime: 2.464us
- IgnoreRows: 0
- OverallThroughput: 190.228515625 KB/sec
- SendRequestTime: 16.551us
- SerializeChunkTime: 4.378us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 40.00 B
- WaitResponseTime: 158.406us
OLAP_SCAN_NODE (id=0):(Active: 739.534us[739534ns], % non-child: 3.53%)
- Table: tem_orders0
- Rollup: tem_orders0
- Predicates: 2: dt >= ‘2022-10-02 00:00:00’
- BytesRead: 28.00 B
- IOTaskCount : 1
- NumDiskAccess: 0
- PeakMemoryUsage: 0.00
- RowsRead: 1
- RowsReturned: 1
- RowsReturnedRate: 1.352K /sec
- ScanConcurrency : 1
- ScanTime: 613.118us
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 609.752us[609752ns], % non-child: 16.57%)
- CachedPagesNum: 0
- CompressedBytesRead: 484.00 B
- CreateSegmentIter: 17.603us
- IOTime: 3.797us
- LateMaterialize: 10.299us
- PushdownPredicates: 1
- RawRowsRead: 2
- ReadPagesNum: 6
- SegmentInit: 569.717us
- BitmapIndexFilter: 491.481us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- SegmentZoneMapFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 11.91us
- BlockFetch: 1.653us
- BlockFetchCount: 1
- BlockSeek: 7.93us
- BlockSeekCount: 1
- ChunkCopy: 430ns
- DecompressT: 0ns
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 461ns
- PredFilterRows: 1
- RowsetsReadCount: 2
- SegmentsReadCount: 1
- TotalColumnsDataPageCount: 3
- UncompressedBytesRead: 334.00 B
Instance 3886392a-7156-11ed-8954-5254001a5303 (host=TNetworkAddress(hostname:10.3.161.9, port:9060)):(Active: 225.974us[225974ns], % non-child: 0.00%)
- AverageThreadTokens: 0.00
- MemoryLimit: 24.00 GB
- PeakMemoryUsage: 0.00
- RowsProduced: 0
DataStreamSender (dst_id=1, dst_fragments=[3886392a715611ed-89545254001a5306]):(Active: 134.823us[134823ns], % non-child: 3.66%)
- PartType: UNPARTITIONED
- BytesSent: 0.00
- CompressTime: 0ns
- IgnoreRows: 0
- OverallThroughput: 0.0 /sec
- SendRequestTime: 31.840us
- SerializeChunkTime: 0ns
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 0.00
- WaitResponseTime: 76.313us
OLAP_SCAN_NODE (id=0):(Active: 113.564us[113564ns], % non-child: 3.09%)
- Table: tem_orders0
- Rollup: tem_orders0
- Predicates: 2: dt >= ‘2022-10-02 00:00:00’
- BytesRead: 0.00
- IOTaskCount : 1
- NumDiskAccess: 0
- PeakMemoryUsage: 0.00
- RowsRead: 0
- RowsReturned: 0
- RowsReturnedRate: 0
- ScanConcurrency : 1
- ScanTime: 3.35us
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:
- CachedPagesNum: 0
- CompressedBytesRead: 0.00
- CreateSegmentIter: 6.482us
- IOTime: 0ns
- PushdownPredicates: 1
- RawRowsRead: 0
- ReadPagesNum: 0
- SegmentInit: 0ns
- BitmapIndexFilter: 0ns
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- SegmentZoneMapFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 0ns
- BlockFetch: 0ns
- BlockFetchCount: 0
- BlockSeek: 0ns
- BlockSeekCount: 0
- ChunkCopy: 0ns
- DecompressT: 0ns
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 0ns
- PredFilterRows: 0
- RowsetsReadCount: 2
- SegmentsReadCount: 0
- TotalColumnsDataPageCount: 0
- UncompressedBytesRead: 0.00
</script-15.sql>

#2

测试数据如下：
insert into dwh_tmp.tem_orders0
select *
from(
select 12345678 order_id ,‘2022-10-01 10:01:01’ dt , 1 cnt
union all select 22345678 order_id ,‘2022-10-02 11:02:02’ dt , 2 cnt
union all select 32345678 order_id ,‘2022-11-01 02:01:01’ dt , 3 cnt
union all select 42345678 order_id ,‘2022-11-02 04:02:02’ dt , 4 cnt
) a

#3

bitmap索引不支持范围查询 dt >= ‘2022-10-02’

#4

是的，按理说位图索引不支持范围检索，但在主键模型中好像支持范围检索
image

– dwh_tmp.tem_orders2 definition

CREATE TABLE tem_orders2 (
order_id bigint(20) NOT NULL COMMENT “”,
dt datetime NOT NULL COMMENT “”,
cnt int(11) NOT NULL COMMENT “”,
INDEX idx_dt1 (dt) USING BITMAP COMMENT ‘test’
) ENGINE=OLAP
PRIMARY KEY(order_id)
COMMENT “OLAP”
DISTRIBUTED BY HASH(order_id) BUCKETS 3
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);Profile1.txt (10.3 KB)

#5

无论什么模型，bitmap索引原理就是不支持范围的。

#6

image
但是看这几个指标，索引生效了，怎么理解呢？

京ICP备2022026421号-1