public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Frédéric Yhuel <[email protected]>
To: [email protected] <[email protected]>
Cc: Jehan-Guillaume de Rorthais <[email protected]>
Cc: Christophe Courtois <[email protected]>
Subject: Re: Indexes on expressions with multiple columns and operators
Date: Wed, 17 Sep 2025 16:22:04 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (26+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Indexes on expressions with multiple columns and operators
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox