Re: Indexes on expressions with multiple columns and operators

On Wed, 2025-09-17 at 15:55 +0200, Frédéric Yhuel wrote:
> Hello, in the following, I don't understand why:
>
> 1) the expression index isn't used in the first EXPLAIN
>
> 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.
>
> (SQL script attached, tested with master and v17)
>
> DROP TABLE IF EXISTS foo;
>
> CREATE UNLOGGED TABLE foo (id bigint, ackid int, crit text);
>
> ALTER TABLE foo ALTER COLUMN crit SET statistics 400;
>
> INSERT INTO foo SELECT i, NULL, CASE WHEN i%100=1 THEN 'WARNING' ELSE
> 'INFO' END FROM generate_series(1,100000) AS T(i);
>
> UPDATE foo SET ackid = random()*10000 WHERE id%100=1 AND id > 500 ;
>
> CREATE INDEX foo_expr_idx ON foo ((ackid IS NULL AND crit = 'WARNING'));
>
> ANALYZE foo ;
>
> EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE
> ackid IS NULL AND crit = 'WARNING';
>                                                QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------
>   Seq Scan on foo  (cost=0.00..1797.00 rows=990 width=17) (actual
> time=0.012..23.932 rows=5.00 loops=1)
>     Filter: ((ackid IS NULL) AND (crit = 'WARNING'::text))
>     Rows Removed by Filter: 99995
> (3 rows)

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

/*
 * Restriction clause info.
 *
 * We create one of these for each AND sub-clause of a restriction condition
 * (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 itself
 * 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 = 'WARNING') IS TRUE;
                                                      QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Index Scan using foo_expr_idx on foo  (cost=0.29..8.39 rows=5 width=17) (actual time=0.011..0.018 rows=5.00 loops=1)
   Index Cond: (((ackid IS NULL) AND (crit = 'WARNING'::text)) = 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