Re: [PATCH] Add planner support function to two-argument regexp_like()

From: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
To: shihao zhong <zhong950419(at)gmail(dot)com>
Cc: jian he <jian(dot)universality(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Add planner support function to two-argument regexp_like()
Date: 2026-11-20 01:21:25
Message-ID: CAHGQGwF1nvAh+Zspg24Qwy6ZSXfiHAH30Z8DW36yArrX4VTd8g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, Oct 9, 2026 at 12:51 PM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> Rebase this patch with master

This patch introduces a row-count estimation regression when expression
index statistics are available for regexp_like(). For example:

-------------------------------------------------
CREATE TABLE t AS
SELECT CASE WHEN i <= 8000 THEN 'abc' ELSE 'xyz' END AS s,
'^a'::text AS p
FROM generate_series(1, 10000) AS g(i);

CREATE INDEX ON t (regexp_like(s, p));
ANALYZE t;

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM t WHERE regexp_like(s, p);
-------------------------------------------------

With the patch, the estimate drops from 8,000 rows to 50, while
8,000 rows actually match. The support function returns the default
selectivity of 0.005 instead of allowing boolvarsel() to use the
expression index statistics.

Other functions, such as starts_with(), already have the same issue.
But, this patch introduces the problem for regexp_like(), so I think
we should address it together?

Regards,

--
Fujii Masao

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2026-11-20 01:49:12 Re: Use WALReadFromBuffers in more places
Previous Message Kyotaro Horiguchi 2026-11-20 00:48:16 Re: A latent pointer increment bug in pgtypes_defmt_scan()