Message-ID: <5c3bbe99acd2dfb9935de8b2aeb246b5e1d2f658.camel@cybertec.at>
Subject: Re: Indexes on expressions with multiple columns and operators
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?ISO-8859-1?Q?Fr=E9d=E9ric?= Yhuel <frederic.yhuel@dalibo.com>,
 "pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>, Christophe Courtois
	 <christophe.courtois@dalibo.com>
Date: Wed, 17 Sep 2025 16:22:04 +0200
In-Reply-To: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/5c3bbe99acd2dfb9935de8b2aeb246b5e1d2f658.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-09-17 at 15:55 +0200, Fr=C3=A9d=C3=A9ric Yhuel wrote:
> Hello, in the following, I don't understand why:
>=20
> 1) the expression index isn't used in the first EXPLAIN
>=20
> 2) the number of estimated rows is completely off in the second EXPLAIN,=
=20
> whereas the planner could easily use the statistics of foo_f_idx.
>=20
> (SQL script attached, tested with master and v17)
>=20
> DROP TABLE IF EXISTS foo;
>=20
> CREATE UNLOGGED TABLE foo (id bigint, ackid int, crit text);
>=20
> ALTER TABLE foo ALTER COLUMN crit SET statistics 400;
>=20
> INSERT INTO foo SELECT i, NULL, CASE WHEN i%100=3D1 THEN 'WARNING' ELSE=
=20
> 'INFO' END FROM generate_series(1,100000) AS T(i);
>=20
> UPDATE foo SET ackid =3D random()*10000 WHERE id%100=3D1 AND id > 500 ;
>=20
> CREATE INDEX foo_expr_idx ON foo ((ackid IS NULL AND crit =3D 'WARNING'))=
;
>=20
> ANALYZE foo ;
>=20
> EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE=20
> ackid IS NULL AND crit =3D 'WARNING';
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN=20
>=20
> -------------------------------------------------------------------------=
------------------------------
> =C2=A0 Seq Scan on foo=C2=A0 (cost=3D0.00..1797.00 rows=3D990 width=3D17)=
 (actual=20
> time=3D0.012..23.932 rows=3D5.00 loops=3D1)
> =C2=A0=C2=A0=C2=A0 Filter: ((ackid IS NULL) AND (crit =3D 'WARNING'::text=
))
> =C2=A0=C2=A0=C2=A0 Rows Removed by Filter: 99995
> (3 rows)

As far as I know, PostgreSQL considers "ackid IS NULL" and "crit =3D 'WARNI=
NG'"
as two different RestrictInfos.  See the comment:

/*
 * Restriction clause info.
 *
 * We create one of these for each AND sub-clause of a restriction conditio=
n
 * (WHERE or JOIN/ON clause).  Since the restriction clauses are logically
 * ANDed, we can use any one of them or any subset of them to filter out
 * tuples, without having to evaluate the rest.  The RestrictInfo node itse=
lf
 * stores data used by the optimizer while choosing the best query plan.

PostgreSQL doesn't consider the (exotic) case what someone creates an index
on an expression that contains an AND.

You could do this:

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE
(ackid IS NULL AND crit =3D 'WARNING') IS TRUE;
                                                      QUERY PLAN           =
                                          =20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90
 Index Scan using foo_expr_idx on foo  (cost=3D0.29..8.39 rows=3D5 width=3D=
17) (actual time=3D0.011..0.018 rows=3D5.00 loops=3D1)
   Index Cond: (((ackid IS NULL) AND (crit =3D 'WARNING'::text)) =3D true)
   Index Searches: 1
(3 rows)

But really, you should create a better index, perhaps

  CREATE INDEX ON foo (crit) WHERE ackid IS NULL;

Yours,
Laurenz Albe