[I] [Bug] Some Query Case unable hit inverted index [doris]

[via GitHub]

PineAppleChan opened a new issue, #38854:
URL: https://github.com/apache/doris/issues/38854

   ### 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
   
   doris-2.1.5
   
   ### What's Wrong?
   
   Table DDL:
   
   ```
    CREATE TABLE `user_sessions` (
     `cname` VARCHAR(1024) NULL,
     `vid` BIGINT NULL,
     `shard` INT NOT NULL,
     `sid` VARCHAR(1024) NULL,
     `finished` BOOLEAN NULL,
     `joinTs` INT NULL,
     `leaveTs` INT NULL,
     `isSender` BOOLEAN NULL,
     `uid` BIGINT NULL,
     `account` VARCHAR(1024) NULL,
     INDEX session_inverted_index (`sessionId`) USING INVERTED,
     INDEX uid_inverted_index (`uid`) USING INVERTED,
     INDEX account_inverted_index (`account`) USING INVERTED,
     INDEX sid_inverted_index (`sid`) USING INVERTED 
   ) ENGINE=OLAP
   UNIQUE KEY(`cname`, `vid`, `shard`, `sid`)
   COMMENT 'OLAP'
   PARTITION BY LIST(`shard`) ()
   DISTRIBUTED BY HASH(`cname`) BUCKETS 15
   ```
   
   
   This query used 50ms, it can hit inverted index (Profile 
RowsInvertedIndexFiltered = 17.189587M）
   ```
   SELECT *
   FROM user_sessions
   WHERE cname IN ('123456')
     AND (
       uid IN (123456)
     )
     AND (
       (finished = true AND joinTs <= 1722826462 AND leaveTs >= 1722740040)
       OR
       (finished = false AND joinTs <= 1722826462) 
     )
   LIMIT 10000;
   ``` 
   
   
   This query used 700ms, it can not hit inverted index (Profile 
RowsInvertedIndexFiltered = 0)
   
   ```
   SELECT *
   FROM user_sessions
   WHERE cname IN ('123456')
     AND (
       uid IN (123456) OR account IN ('123456')
     )
     AND (
       (finished = true AND joinTs <= 1722826462 AND leaveTs >= 1722740040)
       OR
       (finished = false AND joinTs <= 1722826462) 
     )
   LIMIT 10000;
   ```
   
   ### What You Expected?
   
   My table had created an inverted index for the `uid` and `account`.
   The above queries should all be able to hit the inverted index.
   
   
   ### 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-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