richardstartin commented on pull request #8411:
URL: https://github.com/apache/pinot/pull/8411#issuecomment-1082867761
This gives a substantial performance boost, which will be more pronounced on
larger segment sizes and in deployments where the data exceeds RAM, especially
for queries with `in` or `not` clauses:
```
Benchmark (_numRows)
(_query) (_scenario) Mode Cnt Score Error Units
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE INT_COL IN
(0, 1, 2, 3, 4, 5, 7, 9, 10) EXP(0.001) avgt 5 356.283 ± 71.163 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE INT_COL IN
(0, 1, 2, 3, 4, 5, 7, 9, 10) EXP(0.5) avgt 5 7236.655 ± 303.783 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE INT_COL IN
(0, 1, 2, 3, 4, 5, 7, 9, 10) EXP(0.999) avgt 5 6355.539 ± 431.611 us/op
BenchmarkQueries.query 1500000 SELECT
COUNT(*) FROM MyTable WHERE INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.001) avgt 5 404.633 ±
145.129 us/op
BenchmarkQueries.query 1500000 SELECT
COUNT(*) FROM MyTable WHERE INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.5) avgt 5 1545.330 ±
115.749 us/op
BenchmarkQueries.query 1500000 SELECT
COUNT(*) FROM MyTable WHERE INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.999) avgt 5 1268.241 ±
82.946 us/op
BenchmarkQueries.query 1500000 SELECT COUNT(*) FROM MyTable WHERE
INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND LOW_CARDINALITY_STRING_COL =
'value1' AND SORTED_COL BETWEEN 10 and 50 EXP(0.001) avgt 5 465.189 ±
291.468 us/op
BenchmarkQueries.query 1500000 SELECT COUNT(*) FROM MyTable WHERE
INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND LOW_CARDINALITY_STRING_COL =
'value1' AND SORTED_COL BETWEEN 10 and 50 EXP(0.5) avgt 5 2719.248 ±
151.024 us/op
BenchmarkQueries.query 1500000 SELECT COUNT(*) FROM MyTable WHERE
INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND LOW_CARDINALITY_STRING_COL =
'value1' AND SORTED_COL BETWEEN 10 and 50 EXP(0.999) avgt 5 2278.069 ±
144.172 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.001) avgt 5 924.339 ±
112.722 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.5) avgt 5 876.870 ±
120.134 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.999) avgt 5 913.038 ±
119.857 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL <> 'value1' EXP(0.001) avgt 5 5621.019 ±
70.795 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL <> 'value1' EXP(0.5) avgt 5 5636.841 ±
91.966 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL <> 'value1' EXP(0.999) avgt 5 5593.544 ±
146.401 us/op
```
```
Benchmark (_numRows)
(_query) (_scenario) Mode Cnt Score Error Units
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE INT_COL IN
(0, 1, 2, 3, 4, 5, 7, 9, 10) EXP(0.001) avgt 5 392.912 ± 73.485 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE INT_COL IN
(0, 1, 2, 3, 4, 5, 7, 9, 10) EXP(0.5) avgt 5 611.946 ± 95.690 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE INT_COL IN
(0, 1, 2, 3, 4, 5, 7, 9, 10) EXP(0.999) avgt 5 357.086 ± 125.896 us/op
BenchmarkQueries.query 1500000 SELECT
COUNT(*) FROM MyTable WHERE INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.001) avgt 5 372.182 ±
112.726 us/op
BenchmarkQueries.query 1500000 SELECT
COUNT(*) FROM MyTable WHERE INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.5) avgt 5 751.461 ±
125.460 us/op
BenchmarkQueries.query 1500000 SELECT
COUNT(*) FROM MyTable WHERE INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.999) avgt 5 515.615 ±
135.877 us/op
BenchmarkQueries.query 1500000 SELECT COUNT(*) FROM MyTable WHERE
INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND LOW_CARDINALITY_STRING_COL =
'value1' AND SORTED_COL BETWEEN 10 and 50 EXP(0.001) avgt 5 443.027 ±
259.710 us/op
BenchmarkQueries.query 1500000 SELECT COUNT(*) FROM MyTable WHERE
INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND LOW_CARDINALITY_STRING_COL =
'value1' AND SORTED_COL BETWEEN 10 and 50 EXP(0.5) avgt 5 2848.508 ±
271.759 us/op
BenchmarkQueries.query 1500000 SELECT COUNT(*) FROM MyTable WHERE
INT_COL IN (0, 1, 2, 3, 4, 5, 7, 9, 10) AND LOW_CARDINALITY_STRING_COL =
'value1' AND SORTED_COL BETWEEN 10 and 50 EXP(0.999) avgt 5 2268.246 ±
127.690 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.001) avgt 5 122.123 ±
55.815 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.5) avgt 5 124.019 ±
40.301 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL = 'value1' EXP(0.999) avgt 5 121.734 ±
59.327 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL <> 'value1' EXP(0.001) avgt 5 118.834 ±
58.912 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL <> 'value1' EXP(0.5) avgt 5 130.757 ±
60.024 us/op
BenchmarkQueries.query 1500000
SELECT COUNT(*) FROM MyTable WHERE
LOW_CARDINALITY_STRING_COL <> 'value1' EXP(0.999) avgt 5 121.485 ±
44.044 us/op
```
--
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...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
