| 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
| 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() |