Horton
Menu

Re: Indexes on expressions with multiple columns and operators

Frédéric Yhuel <frederic.yhuel@dalibo.com>

View thread
From: Frédéric 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>
Date: 2025-09-17T15:33:35Z
Lists: pgsql-performance 

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Re-allow using statistics for bool-valued functions in WHERE.

  2. Build out the planner support function infrastructure.

  3. Allow planner to use expression-index stats for function calls in WHERE.