On 13/10/2025 16:55, Tom Lane wrote:
Andrei Lepikhov <[email protected]> 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
