Speed up filter read performance for "column ILIKE Utf8("World%")"

SQL

EXPLAIN ANALYZE SELECT subreddit, COUNT(*) AS n FROM reddit_comments WHERE subreddit ILIKE 'World%' GROUP BY subreddit ORDER BY n DESC

SortPreservingMergeExec: [n@1 DESC], metrics=[output_rows=137, elapsed_compute=27.605µs]
  SortExec: expr=[n@1 DESC], preserve_partitioning=[true], metrics=[output_rows=137, elapsed_compute=123.763µs, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0]
    ProjectionExec: expr=[subreddit@0 as subreddit, count(Int64(1))@1 as n], metrics=[output_rows=137, elapsed_compute=9.495µs]
      AggregateExec: mode=FinalPartitioned, gby=[subreddit@0 as subreddit], aggr=[count(Int64(1))], metrics=[output_rows=137, elapsed_compute=512.738µs, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=157128]
        CoalesceBatchesExec: target_batch_size=8192, metrics=[output_rows=1041, elapsed_compute=414.396µs]
          RepartitionExec: partitioning=Hash([subreddit@0], 16), input_partitions=16, metrics=[fetch_time=75.896547271s, repartition_time=406.554µs, send_time=1.557604ms]
            AggregateExec: mode=Partial, gby=[subreddit@0 as subreddit], aggr=[count(Int64(1))], metrics=[output_rows=1041, elapsed_compute=455.441292ms, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, skipped_aggregation_rows=0, peak_mem_used=364832]
              RepartitionExec: partitioning=RoundRobinBatch(16), input_partitions=1, metrics=[fetch_time=4.497096328s, repartition_time=1ns, send_time=66.050701ms]
                MetadataEraserExec, metrics=[]
                  ProjectionExec: expr=[subreddit@0 as subreddit], metrics=[output_rows=6770427, elapsed_compute=14.573308ms]
                    LanceRead: uri=internet/reddit_comments.lance/data, projection=[subreddit], num_fragments=599, range_before=None, range_after=None, row_id=true, row_addr=false, full_filter=subreddit ILIKE Utf8("World%"), refine_filter=subreddit ILIKE Utf8("World%"), metrics=[output_rows=6770427, elapsed_compute=1.422381704s, bytes_read=9959784588, fragments_scanned=599, iops=2344, ranges_scanned=599, requests=2344, rows_scanned=600056376, task_wait_time=1.415853918s]

SQL ILIKE cannot leverage existing BITMAP / BTREE indices, thus fallback to scan whole 600 million rows of the given column and compare one by one.

Optimize to leverage index to speed up the filter criteria.
for LIKE/ILIKE, we could use btrees for anchored prefixes but could also use those trigram indexes

Start by reproducing the provided EXPLAIN ANALYZE query and reading how existing BITMAP and BTREE indexes are used for filters. Investigate anchored-prefix LIKE/ILIKE support and the possible trigram-index approach. Done means the filter can leverage an index instead of scanning all 600 million rows and shows improved read performance.