Query parameters in LIKE Statement causes slow response

Question

I'm trying to query Google spanner with query parameters using node.js client library. However, response is very slower with query parameter than without query parameter. Query has LIKE(forward match) statement. I couldn't find recommended way to use query parameters with LIKE statement.

Additionally, I tested with equal statement, there is no difference between query with parameter and query without parameter.

Table has more than 20 million rows. And instance is 1 node.

Is there any solution? or Is this bug with Google spanner?

Part of Schema(actually more than 40 columns):

CREATE TABLE props (
    props__id STRING(MAX) NOT NULL,
    props__address_quadkey STRING(MAX),
    ...
) PRIMARY KEY (props__id)

Index:

CREATE INDEX props__address_quadkey 
ON props (
    props__address_quadkey
)

Test code:

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const db = spanner
  .instance('instance_name')
  .database('database_name');

(async () => {
  // Make connection
  await db.run({ sql: 'SELECT 1' });


  console.time('Without param');
  const r1 = (await db.run({
    sql: `
      SELECT
        props__id
      FROM props@{FORCE_INDEX=props__address_quadkey}
      WHERE
        (props__address_quadkey LIKE '1330020303011010200%')
    `
  }))[0];
  console.log(r1.length); // 121
  console.timeEnd('Without param'); // Without param: 277.223ms

  console.time('with param 1');
  const r2 = (await db.run({
    sql: `
      SELECT
        props__id
      FROM props@{FORCE_INDEX=props__address_quadkey}
      WHERE
        (props__address_quadkey LIKE @quadkey)
    `,
    params: { quadkey: '1330020303011010200%' },
    types: { quadkey: 'string' },
  }))[0];
  console.log(r2.length); // 121
  console.timeEnd('with param 1'); // with param 1: 9240.822ms
})();

Thank you for your help!

Answer 1

This is currently a limitation of Cloud Spanner. With a constant value for the LIKE pattern, Cloud Spanner is able to optimize the lookup expression based on the LIKE pattern during query compilation. For example, in this case, Cloud Spanner will be able to generate a query plan with a lookup expression that is basically

STARTS_WITH(props__address_quadkey, 1330020303011010200)

which will be able to efficiently search the index for entries that match the prefix in the LIKE pattern.

But with a parameterized LIKE pattern, that is not possible as the parameter is not evaluated until execution time and could contain any LIKE expression. As a result, instead of being able to efficiently lookup the matching rows, Cloud Spanner must read all rows and evaluate them against the LIKE pattern in the parameter to filter out non-matching rows.

This limitation however does not affect simpler predicates like the equality predicate where Cloud Spanner is able to do efficient lookups based on the value of the parameter.