Varbinary列无法使用LIKE查询，求无需转Varchar的优化方案

我在dune.com上执行以下SQL查询：

SELECT
  th.value / 1e18 as amount,
  tr.success,
  th."from",
  th.to,
  tr.hash,
  tr.data,
FROM
  table1 tr
  table2 th ON tr.hash = th.evt_hash
WHERE
  th.to = sjhd21
  AND tr.success = true
  AND (
    th.value / 1e18 > 10
    OR th."from" = h123g
  ) 
  AND CAST(tr.data AS varchar) NOT LIKE '0xbc4b3365%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x447e346f%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x0100670b%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x2d1fb389%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xb9181611%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x47e7ef24%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xdb6b5246%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xf80dec97%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x8129fc1c%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x8da5cb5b%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x7729d644%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xd6c9b6a5%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x143531c0%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x715018a6%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x4fb2e45d%'
  AND CAST(tr.data AS varchar) NOT LIKE '0xf2fde38b%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x4f065632%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x7a78b9c7%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x535b355c%'
  AND CAST(tr.data AS varchar) NOT LIKE '0x9c66c25d%'

该查询处理耗时极长，推测是WHERE子句中转换data列类型所致。若不转换类型，则会报错：

Left side of LIKE expression must evaluate to a varchar (actual: varbinary)

我尝试过用CTE转换data列，但查询速度依旧很慢，因此放弃该方案。请问有没有替代方案？是否存在可直接处理varbinary类型data列的表达式？

针对Dune基于PostgreSQL的SQL环境，提供两种无需全列类型转换的优化方案：

方法1：直接对二进制前缀进行对比 #

利用SUBSTRING提取varbinary列的前4字节（对应以太坊函数签名长度），将目标十六进制前缀转为bytea类型直接对比：

SELECT
  th.value / 1e18 as amount,
  tr.success,
  th."from",
  th.to,
  tr.hash,
  tr.data,
FROM
  table1 tr
  JOIN table2 th ON tr.hash = th.evt_hash
WHERE
  th.to = 'sjhd21'
  AND tr.success = true
  AND (
    th.value / 1e18 > 10
    OR th."from" = 'h123g'
  ) 
  AND SUBSTRING(tr.data, 1, 4) NOT IN (
    '\xbc\x4b\x33\x65'::bytea,
    '\x44\x7e\x34\x6f'::bytea,
    '\x01\x00\x67\x0b'::bytea,
    '\x2d\x1f\xb3\x89'::bytea,
    '\xb9\x18\x16\x11'::bytea,
    '\x47\xe7\xef\x24'::bytea,
    '\xdb\x6b\x52\x46'::bytea,
    '\xf8\x0d\xec\x97'::bytea,
    '\x81\x29\xfc\x1c'::bytea,
    '\x8d\xa5\xcb\x5b'::bytea,
    '\x77\x29\xd6\x44'::bytea,
    '\xd6\xc9\xb6\xa5'::bytea,
    '\x14\x35\x31\xc0'::bytea,
    '\x71\x50\x18\xa6'::bytea,
    '\x4f\xb2\xe4\x5d'::bytea,
    '\xf2\xfd\xe3\x8b'::bytea,
    '\x4f\x06\x56\x32'::bytea,
    '\x7a\x78\xb9\xc7'::bytea,
    '\x53\x5b\x35\x5c'::bytea,
    '\x9c\x66\xc2\x5d'::bytea
  )

核心逻辑：只提取需要匹配的前缀字节，避免全列类型转换，直接用二进制对比，性能远高于转varchar后模糊匹配。

方法2：截取十六进制前缀对比 #

用HEX()将varbinary转为十六进制字符串，但仅截取前8位（对应4字节）进行IN对比，减少字符串处理开销：

SELECT
  th.value / 1e18 as amount,
  tr.success,
  th."from",
  th.to,
  tr.hash,
  tr.data,
FROM
  table1 tr
  JOIN table2 th ON tr.hash = th.evt_hash
WHERE
  th.to = 'sjhd21'
  AND tr.success = true
  AND (
    th.value / 1e18 > 10
    OR th."from" = 'h123g'
  ) 
  AND LEFT(HEX(tr.data), 8) NOT IN (
    'BC4B3365',
    '447E346F',
    '0100670B',
    '2D1FB389',
    'B9181611',
    '47E7EF24',
    'DB6B5246',
    'F80DEC97',
    '8129FC1C',
    '8DA5CB5B',
    '7729D644',
    'D6C9B6A5',
    '143531C0',
    '715018A6',
    '4FB2E45D',
    'F2FDE38B',
    '4F065632',
    '7A78B9C7',
    '535B355C',
    '9C66C25D'
  )

此方法避免了LIKE模糊匹配的性能损耗，仅处理必要的前缀字符，比全列转varchar效率更高。

额外优化点 #

1. 确保tr.hash、th.evt_hash、th.to、tr.success列存在索引，加速关联和过滤逻辑
2. 原SQL中th.to = sjhd21和th."from" = h123g需添加单引号，避免被识别为列名导致错误或性能问题

内容的提问来源于stack exchange，提问作者drum