Message-ID: <9a3389fe-fbc8-4e40-9a35-691bc35d043d@dalibo.com>
Date: Wed, 17 Sep 2025 17:33:35 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Indexes on expressions with multiple columns and operators
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
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>
 <62133334-b844-4d0b-b248-1a8446757e5f@dalibo.com>
Content-Language: 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: <62133334-b844-4d0b-b248-1a8446757e5f@dalibo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9a3389fe-fbc8-4e40-9a35-691bc35d043d%40dalibo.com>
Precedence: bulk


On 9/17/25 16:57, Frédéric Yhuel wrote:
> 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).

BTW, I've also tested another solution: partitioning on 'criticity', 
with one partition for 'INFO' (99% of the rows), and another one for the 
other values ('WARNING' and 'ALARM' in the real case). The statistics 
are much better... however an expression index would be an easier fix.

Multivariate MCV statistics don't work well in the real case (100M lines 
in the table).