Message-ID: <62133334-b844-4d0b-b248-1a8446757e5f@dalibo.com>
Date: Wed, 17 Sep 2025 16:57:26 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Indexes on expressions with multiple columns and operators
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
 Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
 Christophe Courtois <christophe.courtois@dalibo.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
 <1507576.1758120083@sss.pgh.pa.us>
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Content-Language: fr, en-US
Autocrypt: addr=frederic.yhuel@dalibo.com; keydata=
 xjMEXn3bgxYJKwYBBAHaRw8BAQdA6tX5FT/n5ztMWIoBdl6k5avvu65fv6ryfVzIx/aH3V3N
 JEYuIFlodWVsIDxmcmVkZXJpYy55aHVlbEBkYWxpYm8uY29tPsKWBBMWCAA+FiEEi7OTyf6H
 ePS+C6n11XGWhN8zGNUFAmZDVJ8CGwMFCQtfi90FCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
 CgkQ1XGWhN8zGNV65gEA6o+UrebFjn2CIAOYwP3CiiJugiM5TMH9yJyehQua5ZoA/2ChxiVn
 8OUaAw/ErxJmQi1mSN0tNZBL/KhkUPBDUDQNzjgEXn3bgxIKKwYBBAGXVQEFAQEHQCuh5cLQ
 AtEBXHa2Fmtp0kduBu7msM7qO/gaEcdjFUdiAwEIB8J+BBgWCAAmAhsMFiEEi7OTyf6HePS+
 C6n11XGWhN8zGNUFAmZDVJ8FCQtfi90ACgkQ1XGWhN8zGNWOOAEArSqePBqfd4Kx4ulACaWO
 6fM+XSDfUlBAQXx9rU6DR4MBALwozl9g91tRysim6lKxggvBfp/PvbpTZNrxYLWB2fYP
In-Reply-To: <1507576.1758120083@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/62133334-b844-4d0b-b248-1a8446757e5f%40dalibo.com>
Precedence: bulk

Thank you Laurenz and Tom! I'm going to quote Tom's email here:

On 9/17/25 16:41, Tom Lane wrote:
> =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com> 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=# 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.013..0.016 rows=5.00 loops=1)
>     Index Cond: (((ackid IS NULL) AND (crit = 'WARNING'::text)) = true)
>     Index Searches: 1
> (3 rows)
> 

Thanks, it works well indeed.

> but my own tendency would be to use a partial index rather than a
> boolean-valued index:
> 
> regression=# CREATE INDEX foo_partial_idx ON foo (id) WHERE ackid IS NULL AND crit = 'WARNING';
> CREATE INDEX
> regression=# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE ackid IS NULL AND crit = 'WARNING';
>                                                           QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
>   Index Scan using foo_partial_idx on foo  (cost=0.13..107.18 rows=990 width=17) (actual time=0.010..0.014 rows=5.00 loops=1)
>     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.
>

Yes, Laurenz made a similar suggestion, but the problem is that I'm 
mostly interested in the estimated number of output rows... because in 
the real query, there's a very bad Hash Join above (the Nested Loop is 
*much* faster).


>> 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.
> 

OK, thanks anyway, I think the ju-jitsu mentioned above will do, even 
though the application code will have to be patched.