Summary:
The index scan consists of 2 steps.
Fetch ybctids from the index table.
Fetch remaining row data from the indexed table.
On the second step TServer receives list of ybctids from the first step.
Fixed bloom filter cannot be used in this scenario.
Recently variable bloom filter was added, so we could create iterator and defer bloom filter check to iteration phase.
So could use it in index scan.
Manually checked performance improvement:
Started rf=1 cluster on n2-standard-4 dev server.
Created table:
```
CREATE TABLE main (id BIGSERIAL PRIMARY KEY, sub BIGINT, payload TEXT);
CREATE INDEX ON main (sub ASC);
```
Inserted 19M rows (10GB of data on the disk).
With random `sub` in range [0, 9999], random payload of 512 chars size.
Query `EXPLAIN (ANALYZE, DIST, DEBUG) select * from main where sub < 50;` executed multiple times to warmup caches.
I.e. 0.5% of rows fetched.
Master (c85b22b18f8b7d6c671f0f013b1d943b1a520b56):
```
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using main_sub_idx on main (cost=0.00..5.22 rows=10 width=48) (actual time=29.480..2189.527 rows=95208 loops=1)
Index Cond: (sub < 50)
Storage Table Read Requests: 93
Storage Table Read Execution Time: 2122.320 ms
Storage Table Rows Scanned: 95208
Storage Index Read Requests: 93
Storage Index Read Execution Time: 8.512 ms
Storage Index Rows Scanned: 95208
Metric rocksdb_block_cache_miss: 101251.000
Metric rocksdb_block_cache_hit: 291829.000
Metric rocksdb_block_cache_add: 101251.000
Metric rocksdb_block_cache_index_hit: 1302.000
Metric rocksdb_block_cache_data_miss: 101251.000
Metric rocksdb_block_cache_data_hit: 289225.000
Metric rocksdb_block_cache_bytes_read: 9389180864.000
Metric rocksdb_block_cache_bytes_write: 3288284660.000
Metric rocksdb_number_db_seek: 95280.000
Metric rocksdb_number_db_next: 380511.000
Metric rocksdb_number_db_seek_found: 95280.000
Metric rocksdb_number_db_next_found: 380511.000
Metric rocksdb_iter_bytes_read: 225277281.000
Metric rocksdb_block_cache_single_touch_hit: 11505.000
Metric rocksdb_block_cache_single_touch_add: 101251.000
Metric rocksdb_block_cache_single_touch_bytes_read: 373425085.000
Metric rocksdb_block_cache_single_touch_bytes_write: 3288284660.000
Metric rocksdb_block_cache_multi_touch_hit: 280324.000
Metric rocksdb_block_cache_multi_touch_bytes_read: 9015755779.000
Metric docdb_keys_found: 190508.000
Metric rocksdb_read_block_get_micros: sum: 1241347.000, count: 101251.000
Metric rocksdb_sst_read_micros: sum: 1176521.000, count: 101251.000
Metric ql_read_latency: sum: 2923519.000, count: 279.000
Planning Time: 0.057 ms
Execution Time: 2200.769 ms
Storage Read Requests: 186
Storage Read Execution Time: 2130.832 ms
Storage Rows Scanned: 190416
Storage Write Requests: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Flush Requests: 0
Metric rocksdb_block_cache_miss: 101251
Metric rocksdb_block_cache_hit: 291829
Metric rocksdb_block_cache_add: 101251
Metric rocksdb_block_cache_index_hit: 1302
Metric rocksdb_block_cache_data_miss: 101251
Metric rocksdb_block_cache_data_hit: 289225
Metric rocksdb_block_cache_bytes_read: 9389180864
Metric rocksdb_block_cache_bytes_write: 3288284660
Metric rocksdb_number_db_seek: 95280
Metric rocksdb_number_db_next: 380511
Metric rocksdb_number_db_seek_found: 95280
Metric rocksdb_number_db_next_found: 380511
Metric rocksdb_iter_bytes_read: 225277281
Metric rocksdb_block_cache_single_touch_hit: 11505
Metric rocksdb_block_cache_single_touch_add: 101251
Metric rocksdb_block_cache_single_touch_bytes_read: 373425085
Metric rocksdb_block_cache_single_touch_bytes_write: 3288284660
Metric rocksdb_block_cache_multi_touch_hit: 280324
Metric rocksdb_block_cache_multi_touch_bytes_read: 9015755779
Metric docdb_keys_found: 190508
Metric rocksdb_read_block_get_micros: sum: 1241347, count: 101251
Metric rocksdb_sst_read_micros: sum: 1176521, count: 101251
Metric ql_read_latency: sum: 2923519, count: 279
Storage Execution Time: 2130.832 ms
Peak Memory Usage: 24 kB
```
This diff:
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using main_sub_idx on main (cost=0.00..5.22 rows=10 width=48) (actual time=11.148..877.847 rows=95208 loops=1)
Index Cond: (sub < 50)
Storage Table Read Requests: 93
Storage Table Read Execution Time: 811.012 ms
Storage Table Rows Scanned: 95208
Storage Index Read Requests: 93
Storage Index Read Execution Time: 7.985 ms
Storage Index Rows Scanned: 95208
Metric rocksdb_block_cache_hit: 639323.000
Metric rocksdb_block_cache_index_hit: 1302.000
Metric rocksdb_block_cache_filter_hit: 475613.000
Metric rocksdb_block_cache_data_hit: 161106.000
Metric rocksdb_block_cache_bytes_read: 36377052828.000
Metric rocksdb_bloom_filter_useful: 754928.000
Metric rocksdb_bloom_filter_checked: 475613.000
Metric rocksdb_number_db_seek: 95200.000
Metric rocksdb_number_db_next: 380397.000
Metric rocksdb_number_db_seek_found: 95200.000
Metric rocksdb_number_db_next_found: 380397.000
Metric rocksdb_iter_bytes_read: 225165426.000
Metric rocksdb_block_cache_multi_touch_hit: 639323.000
Metric rocksdb_block_cache_multi_touch_bytes_read: 36377052828.000
Metric docdb_keys_found: 190508.000
Metric ql_read_latency: sum: 1109870.000, count: 279.000
Planning Time: 0.054 ms
Execution Time: 889.371 ms
Storage Read Requests: 186
Storage Read Execution Time: 818.997 ms
Storage Rows Scanned: 190416
Storage Write Requests: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Flush Requests: 0
Metric rocksdb_block_cache_hit: 639323
Metric rocksdb_block_cache_index_hit: 1302
Metric rocksdb_block_cache_filter_hit: 475613
Metric rocksdb_block_cache_data_hit: 161106
Metric rocksdb_block_cache_bytes_read: 36377052828
Metric rocksdb_bloom_filter_useful: 754928
Metric rocksdb_bloom_filter_checked: 475613
Metric rocksdb_number_db_seek: 95200
Metric rocksdb_number_db_next: 380397
Metric rocksdb_number_db_seek_found: 95200
Metric rocksdb_number_db_next_found: 380397
Metric rocksdb_iter_bytes_read: 225165426
Metric rocksdb_block_cache_multi_touch_hit: 639323
Metric rocksdb_block_cache_multi_touch_bytes_read: 36377052828
Metric docdb_keys_found: 190508
Metric ql_read_latency: sum: 1109870, count: 279
Storage Execution Time: 818.997 ms
Peak Memory Usage: 24 kB
```
The same test on larger machine (n1-standard-64), where all data fits into block cache.
Master: 1456.097 ms
This diff: 924.507 ms
The test with insert and query 500k rows using index scan (all data fits into memtable):
Master: 1456.097 ms
This diff: 1464.748 ms
Jira: DB-15090
Test Plan: Jenkins
Reviewers: timur
Reviewed By: timur
Subscribers: neil, mihnea, kannan, svc_phabricator, yql, ybase
Tags: #jenkins-ready
Differential Revision: https://phorge.dev.yugabyte.com/D41475
