Indexing "LIKE" in PostgreSQL and Oracle

Indexing a LIKE condition with a b-tree is only possible when the pattern does not begin with a wildcard. A pattern such as %smith may match entries anywhere in the ordered list, so the index would have to be scanned in full. A prefix pattern is different: values that start with the same characters sit next to each other, just as “Black”, “Blackthorn” and “Blacksmith” do in an alphabetically sorted phone book.

That much is true in any database. The differences between PostgreSQL and Oracle show up once collations enter the picture — an area that regularly surprises users coming from Oracle, who expect a plain b-tree index to support LIKE as a matter of course.

Setting up the PostgreSQL example

The test table holds the same strings in three columns, each declared with a different collation: the binary collation C (also known as POSIX), which compares strings byte by byte and never changes; Czech as provided by the operating system's C library; and Czech as provided by ICU.

PostgreSQL does not implement collations itself, with the exception of C; it delegates to the C library or to ICU, a design choice that has its own failure modes.

A handful of rows are inserted, the table is then padded with enough unimportant data that the optimizer will prefer an index scan when one is available, and finally VACUUM and ANALYZE set hint bits and collect statistics.

The binary collation

Under C, indexing LIKE is trivial — a regular b-tree index suffices. PostgreSQL scans the index for everything between 'abc' and 'abd' and removes false positives with a filter, of which there happen to be none here.

The catch is sorting. ORDER BY places the upper-case “N” before the lower-case “a”, and č after every ASCII character. That is unusable for natural language ordering, but if no sorting of strings is required, C is the best collation to use.

Natural language collations and the prefix problem

With ICU-backed s_icu (the libc column behaves identically), sorting is correct. Note where 'abch' lands: after 'abh'. In Czech, the digraph “ch” sorts as a single character, which is why the example uses Czech at all — evidence that natural language collations can be very complicated.

That peculiarity does not change what LIKE means; the SQL standard defines the predicate in ISO/IEC 9075-2, section 8.5 (<like_predicate>).

An index on the column, however, is ignored by PostgreSQL.

The reason is the sorting above: scanning the index range the way C allowed would never find 'abch', because that string lives elsewhere in the order. With some natural language collations, knowing a prefix does not let you locate a string in the index. Not every collation is that complex, but PostgreSQL cannot inspect the internals of collations defined by external libraries, so it must play safe and skip them for this access path.

The PostgreSQL workaround: text_pattern_ops

PostgreSQL lets an index definition name an operator class, fixing the set of comparison operators the index supports. The operator class text_pattern_ops compares strings character by character, which is precisely what LIKE needs, and the execution plan shows those character-wise operators in use.

Such an index does more than support LIKE: it also serves equality comparisons. It is usually the only index a string column needs, unless ORDER BY must be indexed, and that genuinely requires the natural language index.

Reproducing the example in Oracle

The Oracle table mirrors the PostgreSQL one, with string columns declared NOT NULL so that Oracle can use an index to speed up ORDER BY — Oracle stores no index entry in which every column is NULL.

Oracle defaults to the binary collation, which already explains much of the “a plain index is enough” folklore. Two further columns carry the natural language collations Oracle offers:

• CZECH, a reduced form that implements only part of the expected behaviour
• XCZECH, which behaves as a natural language collation should

The Oracle documentation describes the extended variants this way:

Some monolingual collations have an extended version that handles special linguistic cases. The name of the extended version is prefixed with the letter X. These special cases typically mean that one character is sorted like a sequence of two characters or a sequence of two characters is sorted as one character. For example, ch and ll are treated as a single character in XSPANISH. Extended monolingual collations may also define special language-specific uppercase and lowercase rules that override standard rules of a character set.

Loading the data is straightforward: export from PostgreSQL with pg_dump and feed the resulting SQL script to Oracle, which guarantees both systems hold identical data. Optimizer statistics are computed afterwards.

Oracle: sorting versus pattern matching

Under CZECH, the sort order is wrong — as the documentation said, 'abch' is not ordered as it should be. XCZECH fixes it.

An index on s_xczech speeds up the ordered query. Oracle calls an index whose collation differs from the binary one a linguistic index. Parts of the output are omitted for brevity: Oracle appears to implement FETCH FIRST 9 ROWS ONLY with a window function, though presumably it does not read the entire index.

Now the original question: will Oracle use that linguistic index for LIKE?

It will. And the query result shows the cost of that decision — 'abch' is missing, so the answer is simply wrong. With CZECH, the result is correct.

The user therefore has a choice: correct ORDER BY plus incorrect LIKE under XCZECH, or incorrect ORDER BY plus correct LIKE under CZECH. Adding an explicit COLLATE clause yields the correct result, but the author found no way to build an Oracle index that supports that query.

Conclusion

PostgreSQL indexes LIKE through a character-wise operator class when the collation is not binary. Oracle never requires a special index for LIKE — not because it is smarter, but because it is comparatively unconcerned with returning correct results.