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

From: Chengpeng Yan <chengpeng_yan(at)outlook(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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-20 03:05:03
Message-ID: 95566971-D31E-41D0-BCEA-5CF157F1080A@outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

> On Nov 20, 2026, at 02:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> While trying to test this patch, I was reminded that this logic is
> pretty much of a backwater, which likely explains why we've not
> gotten more complaints about it. The only code that actually
> consumes these pattern-analysis estimates is in patternsel_common(),
> and it only uses them if (1) the clause being considered is of the
> form "Var Op Const" but (2) we don't have a histogram of at least
> 100 entries for the Var. I think case (2) is pretty few and far
> between in modern Postgres usage; moreover, you have to have a
> good deal fewer than 100 entries before we rely heavily on the
> estimate rather than the histogram-derived selectivity.
>
> So TBH I'm wondering if this code is worth putting a lot of effort
> into, as opposed to just nuking it all from orbit and using some
> default number when we lack any histogram. Did your interest in it
> stem from any bad real-world experience, and if so what did that
> use-case look like?
>
> regards, tom lane

To be honest, no. I have not hit this in production. As you put it
earlier, "é" is estimated below "e" in UTF-8, and it also depends on the
database encoding. Both looked like defects in the model.

You are right that it is rarely reached, so it does not seem worth
continuing to optimize. Looking for what could replace it, I noticed
that generic_restriction_selectivity() does very nearly the same job:
apply the operator to the MCV and histogram samples, and fall back on a
default when there is little data. It is already used for a number of
other operators.

Reusing it for LIKE and regex would make the encoding dependence
disappear by construction, since nothing counts characters any more, and
several hundred lines of bespoke estimation would go away.

Do you think that direction is workable? If so, I'd like to try it
rather than keep improving logic that applies only in a narrow case.

--
Best regards,
Chengpeng Yan

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Chao Li 2026-11-20 03:28:53 Re: EXPLAIN ANALYZE CREATE TABLE AS EXECUTE runs non-SELECT prepared statements
Previous Message Bharath Rupireddy 2026-11-20 02:56:54 Re: Parallel vacuum: check XID wraparound failsafe between indexes