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>
Subject: Re: Indexes on expressions with multiple columns and operators
In-reply-to: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
Comments: In-reply-to =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?=
 <frederic.yhuel@dalibo.com>
	message dated "Wed, 17 Sep 2025 15:55:29 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1507575.1758120083.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 17 Sep 2025 10:41:23 -0400
Message-ID: <1507576.1758120083@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1507576.1758120083%40sss.pgh.pa.us>
Precedence: bulk

=3D?UTF-8?Q?Fr=3DC3=3DA9d=3DC3=3DA9ric_Yhuel?=3D <frederic.yhuel@dalibo.co=
m> writes:
> Hello, in the following, I don't understand why:
> 1) the expression index isn't used in the first EXPLAIN

The planner doesn't look for multi-clause matches of that sort.
You could apply a little ju-jitsu perhaps:

regression=3D# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM f=
oo WHERE (ackid IS NULL AND crit =3D 'WARNING') is true;
                                                      QUERY PLAN          =
                                            =

--------------------------------------------------------------------------=
--------------------------------------------
 Index Scan using foo_expr_idx on foo  (cost=3D0.29..8.39 rows=3D5 width=3D=
17) (actual time=3D0.013..0.016 rows=3D5.00 loops=3D1)
   Index Cond: (((ackid IS NULL) AND (crit =3D 'WARNING'::text)) =3D true)
   Index Searches: 1
(3 rows)

but my own tendency would be to use a partial index rather than a
boolean-valued index:

regression=3D# CREATE INDEX foo_partial_idx ON foo (id) WHERE ackid IS NUL=
L AND crit =3D 'WARNING';
CREATE INDEX
regression=3D# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM f=
oo WHERE ackid IS NULL AND crit =3D 'WARNING';
                                                         QUERY PLAN       =
                                                   =

--------------------------------------------------------------------------=
---------------------------------------------------
 Index Scan using foo_partial_idx on foo  (cost=3D0.13..107.18 rows=3D990 =
width=3D17) (actual time=3D0.010..0.014 rows=3D5.00 loops=3D1)
   Index Searches: 1
(2 rows)

The advantage of a partial index is you might be able to have the
index entries themselves carry some other column(s), allowing
more queries to be made into index-only scans.  I put "id" here,
which might or might not be of any use in this specific toy example.

> 2) the number of estimated rows is completely off in the second EXPLAIN,=
 =

> whereas the planner could easily use the statistics of foo_f_idx.

Hmm, not sure about that.  Again, boolean-valued indexes aren't
something we've worked on too hard, but I don't see why that
would affect this case.

			regards, tom lane