Enable multithreaded SI in fullscan, but limit threads

[githubmanticore]

With the latest optimizations that I did in SI (#3610) it makes sense to enabled multithreaded execution of fullscan queries with SI. However, there are some caveats. Here's an example:

select request_path, runtime, status, size from logs10m where size > 0 order by runtime desc, size asc limit 20 /*+ secondaryindex(size) */; 
 
threads = 1  0.150 
threads = 2  0.082  1.83x faster 
threads = 3  0.060  2.05x faster 
threads = 4  0.050  3.00x faster 
threads = 5  0.045  3.30x faster 
threads = 6  0.040  3.75x faster 
threads = 7  0.040  3.75x faster 
threads = 8  0.041  3.70x faster 
threads = 9  0.043  3.50x faster 
threads = 10 0.045  3.33x faster 
threads = 11 0.047  3.19x faster 
threads = 12 0.049  3.06x faster 
threads = 13 0.050  3.00x faster 
threads = 14 0.052  2.88x faster 
threads = 15 0.056  2.68x faster 
threads = 16 0.058  2.59x faster 

These are benchmarks from i7-11800H (8/16 cores). Looks like query gets best results when running on 6 cores. When the number of threads exceeds the number of physical cores, performance goes downhill.

We need more benchmarks on other queries. If the results look similar, it makes sense to enable multithreaded execution of fullscan queries with SI, but with some thread limit.

[githubmanticore]

➤ Ilya Kuznetsov commented:

TODO: add benchmarks of FT+SI with thread limit

[githubmanticore]

➤ Ilya Kuznetsov commented:

Some benchmarks of FT+SI:

set global pseudo_sharding=1; select count(*) from hn where match('google') and comment_ranking > 200 
 
threads = 1  0.091 
threads = 2  0.048 
threads = 3  0.033 
threads = 4  0.026 
threads = 5  0.022 
threads = 6  0.019 
threads = 7  0.017 
threads = 8  0.016 
threads = 9  0.016 
threads = 10 0.016 
threads = 11 0.015 
threads = 12 0.015 
threads = 13 0.015 
threads = 14 0.015 
threads = 15 0.015 
threads = 16 0.015 
 
set global pseudo_sharding=1; SELECT * FROM taxi1 where match('harlem east') and trip_distance > 0 AND trip_distance < 5 limit 0 option cutoff=0 /*+secondaryindex(trip_distance)*/; 
 
threads = 1  1.156 
threads = 2  0.771 
threads = 3  0.542 
threads = 4  0.420 
threads = 5  0.358 
threads = 6  0.328 
threads = 7  0.304 
threads = 8  0.293 
threads = 9  0.288 
threads = 10 0.284 
threads = 11 0.290 
threads = 12 0.291 
threads = 13 0.303 
threads = 14 0.316 
threads = 15 0.323 
threads = 16 0.336 

Performance peaks out around 8 threads and either stays the same with more threads or gets worse.

[githubmanticore]

➤ Ilya Kuznetsov commented:

More benchmarks of fullscan+SI:

set global pseudo_sharding=1; SELECT * FROM taxi1 where trip_distance > 0 AND trip_distance < 5 limit 0 option cutoff=0, threads=1 /*+secondaryindex(trip_distance)*/; show meta; 
 
threads = 1  0.418 
threads = 2  0.225 
threads = 3  0.168 
threads = 4  0.143 
threads = 5  0.133 
threads = 6  0.131 
threads = 7  0.134 
threads = 8  0.144 
threads = 9  0.161 
threads = 10 0.173 
threads = 11 0.182 
threads = 12 0.193 
threads = 13 0.196 
threads = 14 0.203 
threads = 15 0.215 
threads = 16 0.237 

As in the previous fullscan+SI query, performance peaks at 6 threads.

[githubmanticore]

➤ Ilya Kuznetsov commented:

Mostly done in f6ea68b1 (cbo_cutoff branch).

[githubmanticore]

➤ Ilya Kuznetsov commented:

Modified CBO to support multithreaded SI in d2425a9

If this results in performance issues, open a new issue.