From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
cc: =?UTF-8?B?RnLDqWTDqXJpYw==?= Yhuel <frederic.yhuel@dalibo.com>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
        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: <20250918172628.44301672@karst>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
 <1507576.1758120083@sss.pgh.pa.us> <20250918172628.44301672@karst>
Comments: In-reply-to Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
	message dated "Thu, 18 Sep 2025 17:26:28 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1975238.1758214751.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 18 Sep 2025 12:59:11 -0400
Message-ID: <1975239.1758214751@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1975239.1758214751%40sss.pgh.pa.us>
Precedence: bulk

Jehan-Guillaume de Rorthais <jgdr@dalibo.com> writes:
> On a fresh instance from HEAD with its default configuration, it shows:

>   Index Scan using foo_s_idx on foo  (cost=3D0.29..8.39 rows=3D33333 wid=
th=3D13)
>     Index Cond: (s(crit, ackid) =3D true)

> It seems statistics shown in "pg_stats" view for function "s()" are good=
. The
> query itself even have the same costs than the query using the syntax ti=
ps you
> provide before.

> However, the estimated row number seems wrong in regard with the costs s=
hown
> and statistics.

Yeah.  The problem is that clause_selectivity_ext fails to consider
use of statistics if the clause looks like "bool_valued_function(...)".
If it looks like "bool_valued_function(...) =3D true", that goes down
a different code path that does the right thing.

Additional factors:

* If you just write "WHERE bool_valued_function(...) =3D true", that
gets stripped down to "WHERE bool_valued_function(...)" in the name
of making equivalent expressions look equivalent.  (IS TRUE doesn't
get stripped, which is why you have to use that wording to avoid
that.)

* Index condition building puts back the "=3D true" in order to
construct something that satisfies the index AM API.  And then it
uses that form to get a selectivity estimate for costing purposes
--- so the right number goes into the indexscan cost estimate.

* But the rowcount estimate is made on the form without "=3D true".
That's the number shown in EXPLAIN and used when considering
joins.

			regards, tom lane