Horton

Thread

Commits

  1. Expand virtual generated columns in constraint expressions

  1. expand virtual generated columns in get_relation_constraints()

    Peter Eisentraut <peter@eisentraut.org> — 2025-09-03T14:05:09Z

    We (I) missed expanding virtual generated columns in 
get_relation_constraints() in plancat.c.  That way, some opportunities 
for constraint exclusion will be missed if a constraint contains virtual 
generated columns, as can be shown in the attached test case (thanks to 
Richard Guo).  Simple fix attached.

  2. Re: expand virtual generated columns in get_relation_constraints()

    Richard Guo <guofenglinux@gmail.com> — 2025-09-11T02:16:29Z

    On Wed, Sep 3, 2025 at 11:05 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> We (I) missed expanding virtual generated columns in
> get_relation_constraints() in plancat.c.  That way, some opportunities
> for constraint exclusion will be missed if a constraint contains virtual
> generated columns, as can be shown in the attached test case (thanks to
> Richard Guo).  Simple fix attached.

I'm afraid this fix isn't thorough: it covers only CHECK constraints
but not NOT NULL or partitioning constraints.  For example,

create table vt (a int, b int generated always as (a * 2) virtual not null);
set constraint_exclusion to on;

explain (costs off) select * from vt where b is null;
         QUERY PLAN
-----------------------------
 Seq Scan on vt
   Filter: ((a * 2) IS NULL)
(2 rows)

We should get a dummy Result rather than a SeqScan.

What I have in mind is something like the attached v2, which covers
all types of constraints.

- Richard

  3. Re: expand virtual generated columns in get_relation_constraints()

    Peter Eisentraut <peter@eisentraut.org> — 2025-09-15T14:36:59Z

    On 11.09.25 04:16, Richard Guo wrote:
> On Wed, Sep 3, 2025 at 11:05 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>> We (I) missed expanding virtual generated columns in
>> get_relation_constraints() in plancat.c.  That way, some opportunities
>> for constraint exclusion will be missed if a constraint contains virtual
>> generated columns, as can be shown in the attached test case (thanks to
>> Richard Guo).  Simple fix attached.
> 
> I'm afraid this fix isn't thorough: it covers only CHECK constraints
> but not NOT NULL or partitioning constraints.  For example,
> 
> create table vt (a int, b int generated always as (a * 2) virtual not null);
> set constraint_exclusion to on;
> 
> explain (costs off) select * from vt where b is null;
>           QUERY PLAN
> -----------------------------
>   Seq Scan on vt
>     Filter: ((a * 2) IS NULL)
> (2 rows)
> 
> We should get a dummy Result rather than a SeqScan.
> 
> What I have in mind is something like the attached v2, which covers
> all types of constraints.

Thanks, I have committed that.