Re: [PATCH] Fix encoding-dependent LIKE/regex selectivity estimates

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chengpeng Yan <chengpeng_yan(at)outlook(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Jeff Davis <pgsql(at)j-davis(dot)com>
Subject: Re: [PATCH] Fix encoding-dependent LIKE/regex selectivity estimates
Date: 2026-11-18 23:08:25
Message-ID: 921783.1795043305@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Chengpeng Yan <chengpeng_yan(at)outlook(dot)com> writes:
> LIKE/regex selectivity estimates for the same pattern depend on the
> database encoding, because FIXED_CHAR_SEL is applied per byte rather
> than per character. For example, `LIKE '%é'` is estimated at 0.2 in
> LATIN1 but 0.04 in UTF8.

I'm not sure what I think about this proposal. I agree it looks dumb
to estimate the selectivity of "é" differently from "e", and even more
so for it to depend on the encoding. However, I believe this per-byte
counting was intentional, with an eye to Far Eastern character sets.
A single ideogram of Chinese or Japanese probably carries about the
same selectivity as a word of English, so giving it the same
selectivity as "e" likewise doesn't sound great. I'm not trying to
claim that per-byte counting is ideal, but there is more here than
meets the eye, so I'm loath to change a longstanding behavior on
the basis of no discussion or evidence. How can we arrive at a
behavior that is sensible for both of those cases?

Some other random comments:

* The fixed_prefix_len correction in regex_selectivity() will
also need adjustment for whatever we do.

* I'm not a fan of removing the case_insensitive flag in
like_selectivity(). Yeah, we've never done anything with it yet,
but it's not the business of a patch like this to decree that
we never will.

* Rather than inserting multiple copies of partial-character-skipping,
I'd be inclined to make the loops advance by characters always.
That will imply making an mb_len call for pattern metacharacters,
but I don't believe the extra cycles are meaningful. This stuff
is not very performance-critical.

* I don't think these test cases are committable as-is: we've
generally found that exposing exact cost estimates results in brittle,
unportable tests. Maybe there's another way, but I'd lean towards
just not bothering. This code is all covered already per
coverage.postgresql.org.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2026-11-18 23:28:25 Re: Use an index scan in RemoveSubscriptionRel()
Previous Message Peter Smith 2026-11-18 23:02:19 Re: Initial table sync copies the whole table when it is no longer published