Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xE1UA-00000000sIA-0E2v for pgsql-general@arkaria.postgresql.org; Tue, 06 Oct 2026 09:29:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xE1U8-0000000GT0a-40Qs for pgsql-general@arkaria.postgresql.org; Tue, 06 Oct 2026 09:29:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xE1U8-0000000GT0S-2en1 for pgsql-general@lists.postgresql.org; Tue, 06 Oct 2026 09:29:20 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xE1U6-00000000ghZ-2P96 for pgsql-general@lists.postgresql.org; Tue, 06 Oct 2026 09:29:20 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-c315869241fso207698566b.0 for ; Tue, 06 Oct 2026 02:29:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1791278956; x=1791883756; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:content-type:from:to:cc :subject:date:message-id:reply-to:content-type; bh=ZB2RaDpjJc88PX8vhCqrNtqJY2fAA9AY1b7+rHQ0DQ4=; b=PzlwD7NEg33ZPBZtJLMFdshC/6GOPPB4M9ckpOGXvB7RVtFOsRDOrtetq82hemM3NW KHJmLk4vbHr6W7rLCV58YOI4LtYg9yIEfItcfkILVyOxmpNwEY/pMcp6k77iLMi53K44 xzv0jeTpgISFv4pyY4VkTPHrfZKXZRi3JSrOWu8XWNug2Rv5HFr0tRD43cSfT+qFH+mU Aqy+PLWLIRbcztc3HABKSGeX4asPl8Eh/QfGg3jaHDlAhjx+RiugKQ99T9UibTuhptad GMeQ5Ggsnykh3z6oPEKDMSOxwrRnJx13gLS8QjuCGREZNiDlASpDz/dkA8lykXHZ3PdB dVQA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1791278956; x=1791883756; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:content-type:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=ZB2RaDpjJc88PX8vhCqrNtqJY2fAA9AY1b7+rHQ0DQ4=; b=mFDgw4p9SHEOTSR6TplQCORg434eFkZCrYHbrJPDJIf/lcs/lpdWYLr1A2l9oXK2sR 6Fv2+r+t1dK/6KKdFMqWOuAJ+ibywvFKAngOa4mPepdxmwsKbaCKkC5UZCV3hV5N3R2i +dwIC6OU4PD8DZu8LLgYJt0YjOn1DfAuwZw9TTozSfbYlcAhb244haeWglODRV/wj5vN ZFz8gt9UNrYjUrLmWlzmDcemwt0tu1JDSd4qJxNe/pcE/tTVhUzEib6ez71Byxalk7tR 8GvTLpdHJ2Op7q6hZIQjsNo0jh/+TtGN48C1Z8ojZx1EjSSlJwld9YaX9weIQOSjTVf6 98Wg== X-Gm-Message-State: AFq9FYJlv+1yLJMWwXVyzoZ+PjHeKYthGLLucfZyqae+u7a9BPMRky/m 4mOtJAOml5pUdjdszvvyrY/kO5wt+pDy379xDq0u1xYNeBKTR/ImWJ3TPsD2YQ== X-Gm-Gg: AYBFou3YK0Ypbf6Qfebr0oKlnNez768yhBqx7PIiqH1EBorYFBWdzC1OtBw4YOVgynh wzV7YTmKTZlcBUEYXmOOiLGym9zWyUeukfIL95LSsa73goqStYkXWh0mWJD0BM/DhKmAevudjIL xaGbS5BTD1mBibnUpRUNaALg+JeaKioXibJ0a+k6gv04C1KwXcRQJkbfq8K607zNi/+3RKHFVg/ UpRnPMmBjCjK7ROIDYx401UJmylFeAuDvwv8uR/fyyr588NHcbTsVPejs3/oPZ04O7n0CYlDjex LnVj1GnZ/zf+ZaCKQ8y6Sdoy085cwFCy5vnm1dXty0NyqBtnOBYAXxcw7bl42o2wcAD3qRc3QlH JQ4zLx7+MgLj9/gQqz4BkkLR/vOHsXjWeWkQPfrQDgYNRl3CNLoEAzveW2cG+gOdkMSV0IEHlor zS4UR9oqTEvLfOwyGEbNOh4uvXkwOz+s4S0QGw5UB0oUlm3n3aG1lIznU8FQou8yn2zmUViT5bd oTude0PjVlxtXZFA1abOzDxXgf3jnO/luOwy6gs8dFAygX7K+R5ZCdSJ8icJtTb9rdODw== X-Received: by 2002:a17:907:72d2:b0:c25:5d2:acf0 with SMTP id a640c23a62f3a-c2e6edd8780mr945357566b.8.1791278956383; Tue, 06 Oct 2026 02:29:16 -0700 (PDT) Received: from smtpclient.apple (84-104-229-211.cable.dynamic.v4.ziggo.nl. [84.104.229.211]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-c31695121bdsm35428066b.33.2026.10.06.02.29.15 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 06 Oct 2026 02:29:15 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.700.51.1.1\)) Subject: Re: Parsing of hex encoding strings From: Thomas de Zeeuw In-Reply-To: <1f2d35acc290d5644cac1217048745ff156e4af0.camel@cybertec.at> Date: Tue, 6 Oct 2026 11:29:05 +0200 Cc: pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <89F55635-31B8-42E2-8DAA-AA5548C05A2E@gmail.com> References: <088bd47f443ee4b8f657e3dbfadf611a560aa95d.camel@cybertec.at> <1f2d35acc290d5644cac1217048745ff156e4af0.camel@cybertec.at> To: Laurenz Albe X-Mailer: Apple Mail (2.3864.700.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Laurenz, > On 5 Oct 2026, at 16:10, Laurenz Albe = wrote: >=20 > On Mon, 2026-10-05 at 12:58 +0200, Thomas de Zeeuw wrote: >> Not great that the first thing I read that this a design error, = especially since >> you=E2=80=99re not familiar with the entire system. In any case, = allow me to expand on >> why we do it this way. >=20 > I could only go by what you told us, and as you said "strings", I = assumed you were > talking about character strings, not binary data. Full-text search is = no good for > binary data, even if encoded - it is only useful for natural language = strings. >> We have various columns that we want to search, most of them are = English text >> (or at least some kind of human language). However, one of them is a = public key >> encoded as a hex string. We want to search all those columns in = single query >> and since most of them are English text we decided on full text = search. We >> imagined one of the words in the search vector would simply be the = hex encoded >> public key. This actually works pretty well for our use case, with = the exception >> of the case I=E2=80=99ve described in the original email. >>=20 >> The trigram search doesn=E2=80=99t work well for the English text = columns we have. >> We could do the trigram search just for the hex encoded public key, = but we >> thought it would return too many false positives given that we=E2=80=99= re only >> interested in exact and prefixed matches. The similarity searching = property >> of trigrams would be negative here as we could include public keys = that=E2=80=99s >> we're not searching for. >=20 > If all you want to do is prefix matches and exact matches, I would use = an > ordinary LIKE with a regular B-tree index. That should work well, = unless > the public keys are large. In that case, you could try the ^@ = operator > and an SP-GiST index. Or resort to trigrams. You=E2=80=99re right. Since it=E2=80=99s only a single we didn=E2=80=99t = really want to bother with having to do filter based on search data or = an another operator (e.g. LIKE like you suggest). We also use ranking = and having to somehow include the match of this hex string in that = function would have been challenging I think. So we took the easy route = and included it in the full text search vector. For our use case this = works well, even if it=E2=80=99s not designed for it. >=20 >> We=E2=80=99ll resort to converting the hex encoded public key to a = vector/query >> by casting instead of using the various functions for this. >=20 > No idea what a vector/query is, but good if it works for you. I should have been more clear, I was referring to the tsquery and = tsvector types. >=20 > Yours, > Laurenz Albe Thank you again Laurenz for taking time to help a random stranger on the = internet.