Re: [PG19] Wrong results from NOT NULL-based expression simplification

From: shihao zhong <zhong950419(at)gmail(dot)com>
To: Richard Guo <guofenglinux(at)gmail(dot)com>
Cc: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Melanie Plageman <melanieplageman(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Peter Eisentraut <peter(at)eisentraut(dot)org>
Subject: Re: [PG19] Wrong results from NOT NULL-based expression simplification
Date: 2026-10-17 04:13:37
Message-ID: CAGRkXqSQT4CMmOUXDYBoxyMXdU6DqXMQqb-LPyrPTx-kDtJeYQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Richard,

With v2 this scans both partitions, on master only pc1.

CREATE FUNCTION pf(numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE AS 'SELECT $1';
CREATE TABLE pc (a numeric(10), b numeric(10))
PARTITION BY LIST ((COALESCE(pf(a), b)));
CREATE TABLE pc1 PARTITION OF pc FOR VALUES IN (1);
CREATE TABLE pc2 PARTITION OF pc FOR VALUES IN (2);
EXPLAIN (COSTS OFF) SELECT * FROM pc WHERE COALESCE(pf(a), b) = 1;

This is because the key is RelabelType, partprune.c shard it only from the
clause.

Thanks,
Shihao

Attachment Content-Type Size
nocfbot-0001-Shard-relabeling-from-the-partition-key-when-matching.patch application/octet-stream 3.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message ZizhuanLiu X-MAN 2026-10-17 04:18:41 Re: Optimize MCV stats for sortable types and utilize sorted-order properties
Previous Message Tom Lane 2026-10-17 04:13:00 Re: First draft of PG 19 release notes