From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
        Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
        Christophe Courtois <christophe.courtois@dalibo.com>,
        Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Indexes on expressions with multiple columns and operators
In-reply-to: <76fbd9ef-1fbb-49f0-bc8b-844462b18d1d@dalibo.com>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
 <1507576.1758120083@sss.pgh.pa.us>
 <62133334-b844-4d0b-b248-1a8446757e5f@dalibo.com>
 <1916727.1758209549@sss.pgh.pa.us> <1972974.1758213639@sss.pgh.pa.us>
 <76fbd9ef-1fbb-49f0-bc8b-844462b18d1d@dalibo.com>
Comments: In-reply-to =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?=
 <frederic.yhuel@dalibo.com>
	message dated "Fri, 19 Sep 2025 09:50:26 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2432872.1758387110.1@sss.pgh.pa.us>
Date: Sat, 20 Sep 2025 12:51:50 -0400
Message-ID: <2432873.1758387110@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2432873.1758387110%40sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com> writes:
> On 9/18/25 18:40, Tom Lane wrote:
>> The attached fixes things so it works like it did pre-a391ff3c3.

> Indeed, it works well!

Thanks for testing!

>> I spent some time trying to devise a test case, and was reminded
>> of why I didn't have one before: it's hard to make a case that
>> will be robust enough to not show diffs in the buildfarm.
>> I'll keep thinking about that though.

> It looks like an interesting homework for an aspiring hacker :-)
> So I'll try to devise one, too.

I concluded that maybe I was overthinking this part.  We only really
need to check the rowcount estimate, since the indexscan cost estimate
is already okay.  And stats_ext.sql seems to have gotten away with
assuming that rowcount estimates are reliably reproducible on
not-too-large tables.  This bug affects use of extended statistics
too, so a test using those is good enough to show it's fixed; we don't
really need to use an expression index for the purpose.  So, I added a
test case in stats_ext.sql and pushed it.

I wish we could back-patch this, since it's so obviously a bug fix.
But we avoid changing plans in released branches, and at this point
it's even too late for v18.

Thank you again for the report.

			regards, tom lane