Hi Team , Pinot version : 1.2.0 Why is bloom fil...
# troubleshooting
a
Hi Team , Pinot version : 1.2.0 Why is bloom filters not mentioned in the query plan ?
Copy code
BROKER_REDUCE(limit:10)	1	0
COMBINE_SELECT	2	1
PLAN_START(numSegmentsForThisPlan:27)	-1	-1
SELECT(selectList:symbolName, ingestionTime)	3	2
PROJECT(ingestionTime, symbolName)	4	3
DOC_ID_SET	5	4
FILTER_AND	6	5
FILTER_FULL_SCAN(operator:IN,predicate:exchSeg IN ('nse','bse'))	7	6
FILTER_FULL_SCAN(operator:EQ,predicate:ordStatus = 'complete')	8	6
FILTER_INVERTED_INDEX(indexLookUp:inverted_index,operator:EQ,predicate:userId = 'ABC')	9	6
PLAN_START(numSegmentsForThisPlan:12)	-1	-1
ALL_SEGMENTS_PRUNED_ON_SERVER	3	2
PLAN_START(numSegmentsForThisPlan:79)	-1	-1
SELECT(selectList:symbolName, ingestionTime)	3	2
PROJECT(ingestionTime, symbolName)	4	3
DOC_ID_SET	5	4
FILTER_EMPTY
Copy code
SELECT symbolName, ingestionTime 
FROM amxordersgpx_uf 
WHERE userId = 'ABC' 
AND exchSeg IN ('nse', 'bse') 
AND ordStatus = 'complete'
AND ingestionTime < 1741631400000
Assuming it as a query plan issue and not execution.
@Xiang Fu ^^
Should I create an issue as I can see bloom filter is available under the segment index info
m
It probably is due to inv index being used for userid. However, if most your queries are for single user, sorting and partitioning on it will be much better
a
Ideally it should leverage bloom filter to filter out segments and then use inverted index in each segment to locate the record. I dont think it is about prioritization. This table has other type of queries too.
g
Bloom filters are not part of the explain plan.
in fact no segment pruning technique is part of the plan
You wont see in the plan info related to partition or min max pruning for example. That info is only included in query metadata, which says how many segments have been prunned
a
Okay - struggling to get p95 below 100 ms , 5000 segments and around 600 million records
5000 qps is required
Data is spread accross 9 servers with 12 cores each
m
You need to sort + partition
userId
if all queries are for a specific user (
userId = 'ABC'
). Use Timestamp index (store in second or hour, or day, whatever you will query on, not millis). This will give you 5k or more
x
as well, bloomfilter might help to prune segments before looking into the dictionary
for your query, how many data/rows will be selected after the predicate
can you do one check, which is to run
select count(*)
query with the predicate
and check if that query could be below p95< 100ms
a
Yes - I am using Bloom filters too - I was able to achieve this too . There were few smaller segments which were causing issue. Anyway we are also in talks with Startree already. I suppose the issue might be using gp3 . I guess we need better ssd. Till 2k qps p99 is less than 50 ms which is working for us for now. As that will be the peak load for now.
x
true, nvme ssd will bring down your cost and latency
👍