Optimize symbol like/ilike/regexp filters when the number of distinct values is high

To reproduce

Continuation of #4825

This query

WITH first_selection as (
  SELECT
    pair,
    first(rate) AS open,
    last(rate) AS close,
    min(rate) AS low,
    max(rate) AS high,
    sum(volume_a) AS base_volume,
    sum(
      CASE
        WHEN buyer = taker then volume_a
        else 0
      END
    ) as base_volume_buy,
    sum(
      CASE
        WHEN seller = taker then volume_a
        else 0
      END
    ) as base_volume_sell,
    sum(volume_b) AS counter_volume,
    sum(
      CASE
        WHEN seller = taker then volume_b
        else 0
      END
    ) as counter_volume_buy,
    sum(
      CASE
        WHEN buyer = taker then volume_b
        else 0
      END
    ) as counter_volume_sell,
    count(*) AS exchanges,
    count_distinct(buyer) as unique_buyers,
    count_distinct(seller) as unique_sellers,
    last(ts) as last_trade
  FROM
    xrpl_offer_exchanges
  WHERE
    ts >= '2024-09-06T07:56:15.571Z'
    AND ts <= '2024-09-13T07:56:15.571Z'
    AND (
      (
        pair not like 'XRP|%'
        OR (
          pair like 'XRP|%'
          AND volume_a >= 0.00001
        )
      )
      AND (
        pair not like '%|XRP'
        OR (
          pair like '%|XRP'
          AND volume_b >= 0.00001
        )
      )
    )
),
second_selection as (
  SELECT
    pair,
    rate as prev_rate,
    ts as prev_ts
  FROM
    xrpl_offer_exchanges
  WHERE
    ts < '2024-09-06T07:56:15.571Z'
    AND pair in (
      SELECT
        pair
      FROM
        first_selection
    ) LATEST ON ts PARTITION BY pair
)
SELECT
  first_selection.pair,
  first_selection.open,
  first_selection.close,
  first_selection.low,
  first_selection.high,
  first_selection.base_volume,
  first_selection.base_volume_buy,
  first_selection.base_volume_sell,
  first_selection.counter_volume,
  first_selection.counter_volume_buy,
  first_selection.counter_volume_sell,
  first_selection.exchanges,
  first_selection.unique_buyers,
  first_selection.unique_sellers,
  first_selection.last_trade,
  second_selection.prev_rate,
  second_selection.prev_ts
from
  first_selection
  LEFT JOIN second_selection on (pair)
WHERE
  first_selection.pair LIKE '%|%';

Executes in 2.7s with 8.0.3 and in 4.5s with 8.1.1. We should look into ways of having equal exec times, i.e. getting rid of any perf regressions around AbstractLikeSymbolFunctionFactory.ConstContainsStaticSymbolTableFunction and other similar functions for LIKE/ILIKE/regexp over a static symbol table.

QuestDB version:

8.1.1

OS, in case of Docker specify Docker and the Host OS:

Linux

File System, in case of Docker specify Host File System:

ext4

Full Name:

Andrei Pechkurov

Affiliation:

QuestDB

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response

Start by reproducing the provided SQL query and comparing execution on QuestDB 8.0.3 and 8.1.1. Read AbstractLikeSymbolFunctionFactory.ConstContainsStaticSymbolTableFunction and the other LIKE, ILIKE, and regexp functions for static symbol tables. Done means identifying and addressing the regression, with execution time returning to the earlier result or otherwise showing no comparable slowdown.