Re: extended stats on partitioned tables

On 12/12/21 22:32, Justin Pryzby wrote:
> On Sun, Dec 12, 2021 at 05:17:10AM +0100, Tomas Vondra wrote:
>> The one thing bugging me a bit is that the regression test checks only a
>> GROUP BY query. It'd be nice to add queries testing MCV/dependencies
>> too, but that seems tricky because most queries will use per-partitions
>> stats.
>
> You mean because the quals are pushed down to the scan node.
>
> Does that indicate a deficiency ?
>
> If extended stats are collected for a parent table, selectivity estimates based
> from the parent would be better; but instead we use uncorrected column
> estimates from the child tables.
>
> From what I see, we could come up with a way to avoid the pushdown, involving
> volatile functions/foreign tables/RLS/window functions/SRF/wholerow vars/etc.
> > But would it be better if extended stats objects on partitioned
tables were to
> collect stats for both parent AND CHILD ? I'm not sure. Maybe that's the
> wrong solution, but maybe we should still document that extended stats on
> (empty) parent tables are often themselves not used/useful for selectivity
> estimates, and the user should instead (or in addition) create stats on child
> tables.
>
> Or, maybe if there's no extended stats on the child tables, stats on the parent
> table should be consulted ?
>

Maybe, but that seems like a mostly separate improvement. At this point
I'm interested only in testing the behavior implemented in the current
patches.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company