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