Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-42201

VEC_FromText() with a constant argument is evaluated once per row in the SELECT list and ORDER BY, but only once in WHERE

    XMLWordPrintable

Details

    Description

      Problem

      A nearest-neighbour query on a table without a vector index is much slower than it should be. The reason: a constant VEC_FromText('...') is converted again for every row when it is used in the SELECT list or in ORDER BY. In WHERE it is converted only once.

      Measured on real text embeddings: 20,000 rows, VECTOR(768), MariaDB 12.3.3, one client, median of 3 runs. Query:

      SELECT id, VEC_DISTANCE_COSINE(embedding, VEC_FromText('[... 768 numbers ...]')) AS distance
      FROM t ORDER BY distance LIMIT 10;
      

      How the query vector is written Time (same 10 rows returned)
      VEC_FromText of the text, in SELECT / ORDER BY about 1,080 ms
      SET @q = VEC_FromText(...) once, then use @q 23-26 ms
      binary float32 as a hex literal x'...' 23-26 ms
      binary value as a parameter, CAST(? AS BINARY) 23-26 ms
      VEC_FromText of the text, in WHERE (count query) about 24 ms

      That is about 53 microseconds per row for a text of 768 numbers. The sort is not the cause: ANALYZE FORMAT=JSON shows r_used_priority_queue: true, and a much larger sort buffer did not change the time. My guess, not checked in the source code: a constant sub-expression is cached in WHERE but not in the SELECT list or in ORDER BY.

      How to reproduce

      All 20,000 rows hold the same vector, so every distance is 0. This is only a timing test. It needs the sequence engine (seq_1_to_N).

      CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, embedding VECTOR(768) NOT NULL);
      SET @vtext = CONCAT('[', (SELECT GROUP_CONCAT(ROUND(RAND()-0.5, 6)) FROM seq_1_to_768), ']');
      INSERT INTO t (embedding) SELECT VEC_FromText(@vtext) FROM seq_1_to_20000;
       
      -- slow: the constant VEC_FromText() is converted for every row
      SET @s = SYSDATE(6);
      SELECT COUNT(*) FROM (SELECT id, VEC_DISTANCE_COSINE(embedding, VEC_FromText(@vtext)) d FROM t ORDER BY d LIMIT 10) x;
      SELECT TIMESTAMPDIFF(MICROSECOND, @s, SYSDATE(6)) / 1000 AS ms;
       
      -- fast: converted once
      SET @q = VEC_FromText(@vtext);
      SET @s = SYSDATE(6);
      SELECT COUNT(*) FROM (SELECT id, VEC_DISTANCE_COSINE(embedding, @q) d FROM t ORDER BY d LIMIT 10) x;
      SELECT TIMESTAMPDIFF(MICROSECOND, @s, SYSDATE(6)) / 1000 AS ms;
      

      Result on a laptop (MariaDB 12.3.3 in Docker, two runs each): slow 956 ms and 938 ms; fast 23.8 ms and 24.8 ms.

      Why it matters

      The Laravel framework generates this shape for its vector queries (vec_distance_cosine(col, vec_fromtext) with a bound text parameter), so every Laravel user is affected. With a vector index the extra cost is smaller but visible: on 50,000 rows (M=16, default search effort) the indexed query took about 2.6 ms with the text form and about 0.7 ms with a binary vector (this includes some client-side work). Without an index on 50,000 rows: 2,725 ms with the text form, 58 ms with a binary vector.

      Expected

      A constant VEC_FromText(<const>) is evaluated once per statement wherever it appears, as it is in WHERE.

      Related issues (not duplicates)

      MDEV-40145 speeds up the distance calculation itself in brute-force scans. MDEV-36205 speeds up distance calculations by extrapolation. Neither mentions VEC_FromText.

      Not tested

      Other versions (only 12.3.3), other dimensions (only 768), and queries that use the vector index with a filter.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              rhaima96 Mohamed Touhami Rhaima
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.