Horton

Re: extended stats on partitioned tables

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Zhihong Yu <zyu@yugabyte.com>, Jaime Casanova <jcasanov@systemguards.com.ec>, pgsql-hackers@postgresql.org
Date: 2021-12-12T22:23:19Z
Lists: pgsql-hackers 

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

Commits

  1. Add stxdinherit flag to pg_statistic_ext_data

  2. Build inherited extended stats on partitioned tables

  3. Ignore extended statistics for inheritance trees

  4. Don't build extended statistics on inheritance trees

  5. Tighten up relation kind checks for extended statistics

  6. Avoid assuming that statistics for a parent relation reflect the properties of