# Postgres large sub-string query performance

DevFeed: [Postgres large sub-string query performance](<https://devfeed.tech/articles/postgres-large-sub-string-query-performance-20756.md>)

Original publisher: [Read original article](<https://www.evanjones.ca/postgres-large-string-performance.html>)

Published: 2022-02-27T21:47:01Z

Content type: article

Language: en

Sources: [Evan Jones](<https://devfeed.tech/sources/evan-jones.md>)

Topics: [benchmarking](<https://devfeed.tech/topics/benchmarking.md>), [Benchmark](<https://devfeed.tech/topics/benchmark.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>)

Tags: [benchmark](<https://devfeed.tech/tags/benchmark.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>)

## AI overview

A benchmark of PostgreSQL substring queries on large variable-length strings finds that substring searches are about six times slower than HSTORE or JSONB key lookups. Regular expressions are about three times slower than LIKE, BYTEA queries are faster than TEXT queries, and inline compressed TOAST storage outperforms uncompressed and out-of-line storage in the tested workload.

## Source excerpt

Following up on my last post about large JSON queries, I also benchmarked sub-string queries on large variable-length strings. I wanted to check if sub-string queries might be faster than HSTORE or JSONB key lookups. I tested both binary (BYTEA) and Unicode text (TEXT). Unfortunately, Postgres sub-string queries are about 6x slower than HSTORE or JSONB key queries. I also learned that Postgres regular expressions are extremely slow: about 3x slower than using LIKE. Queries on BYTEA are faster than queries on TEXT. Perhaps the most interestingly, TOAST inline compressed storage was faster than the uncompressed storage. However, similar to my results with JSON values, out-of-line storage using a separate TOAST table is quite a bit slower than inline storage. I spent a bit of time looking at the code that implements Postgres's LIKE operator and the implementation of the POSITION function. I'm pretty sure they could be made quite a bit faster, at least for UTF-8 or binary strings. For example, the BYTEA implementation of POSITION is a function called byteapos. It implements the simple O(nm) implementation, where for each byte of the string, you compare it to the substring. There are much faster implementations that can use SIMD instructions. I think there is an opportunity to make POSITION() and LIKE substantially faster. Query performance benchmark For details on the benchmark setup, see my previous article. In this case, I tested TEXT and BLOB columns that are near the 2004 byte limit for inline uncompressed tuples. I measured querying substrings that either did not match, or matched at the very end, which should represent the "worst case" search times. I tested LIKE, regular expressions (~ operator), and the POSITION() function. The entire workload was in memory, and parallel queries were disabled. For more details, see the benchmark source code in Github, which links to a Google sheet with the raw results. Fastest query times (ms) This table shows the fastest query