Message-ID: <f129ab71-2684-49fb-ae43-3a82ff34728f@gmail.com>
Date: Tue, 14 Oct 2025 11:29:27 +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: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>,
 "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>
 <1916727.1758209549@sss.pgh.pa.us> <1972974.1758213639@sss.pgh.pa.us>
 <76fbd9ef-1fbb-49f0-bc8b-844462b18d1d@dalibo.com>
 <2432873.1758387110@sss.pgh.pa.us>
 <6ad08c0c-2c03-4b30-b5a7-dc486aa29c52@dalibo.com>
 <b1c2112a-5f1a-4b73-9b21-69d342fad5b0@gmail.com>
 <493a013c-63d1-467a-b9ec-352f77baf37a@dalibo.com>
 <9756a3e9-5dea-4506-a077-72a4d9c302df@gmail.com>
 <c7304dbd-ce2a-40c8-b267-d2c95abfc36e@dalibo.com>
 <6754b8f8-6dad-4778-8c10-b3aed4b495e4@dalibo.com>
 <22c38fe8-e673-46ec-b025-6ea964b3502f@gmail.com>
 <228091.1760367307@sss.pgh.pa.us>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <228091.1760367307@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f129ab71-2684-49fb-ae43-3a82ff34728f%40gmail.com>
Precedence: bulk

On 13/10/2025 16:55, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.com> writes:
>> On 25/9/2025 12:41, Frédéric Yhuel wrote:
>>> So, on SQL Server, you can do this:
>>> CREATE STATISTICS FooStats ON foo (ackid, crit) WHERE crit = 'WARNING';
> 
>> Nice! Thanks for the report. I think the only reason why Postgres
>> doesn't have it yet is the computational cost.
> 
> I think it's more lack of round tuits.  If we had such an option for
> statistics objects, presumably we'd determine the applicability of a
> particular statistics object to a query the same way we do for partial
> indexes, namely try to prove the statistics' restriction condition
> from the query WHERE clauses.  I've not heard complaints about that
> being unduly expensive.
> 
> In the meantime, I believe the old-fashioned approach of creating
> a partial expression index and letting ANALYZE collect stats on that
> will serve, at least for simple statistics.I know at least two extensions (one of which is mine) that attempt to 
analyse query post-execution state, identify unsuccessful predictions on 
cardinality, number of groups, and work_mem, and fix these issues by 
creating MCV and distinct extended statistics.

Of course, without extended statistics on join clauses, their effect is 
highly limited, but we are preparing ;).

Many combinations of clauses may occur. Partial indexes can affect the 
whole system's performance in automatic mode. Additionally, I would 
personally like to play the same game as SQL Server already does - 
compute statistics in an efficient manner - during a Scan. The filter of 
such a scan may serve as a WHERE condition in the extended statistics.

-- 
regards, Andrei Lepikhov,
pgEdge