Message-ID: <d11ae731-4366-ee5f-9ffd-0bd61f5e94a6@enterprisedb.com>
Date: Sun, 12 Dec 2021 23:23:19 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
Subject: Re: extended stats on partitioned tables
Content-Language: en-US
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Zhihong Yu <zyu@yugabyte.com>,
 Jaime Casanova <jcasanov@systemguards.com.ec>, pgsql-hackers@postgresql.org
References: <20210925223152.GA7877@telsasoft.com>
 <20210926202550.GA831@telsasoft.com> <20211007202646.GA9522@ahch-to>
 <20211007224543.GH27491@telsasoft.com>
 <46f37069-55cb-db6a-a15a-e351a2606592@enterprisedb.com>
 <20211103231904.GP17618@telsasoft.com>
 <689accb1-76e8-5115-6ed0-336749cdca57@enterprisedb.com>
 <20211203052411.GU17618@telsasoft.com>
 <CALNJ-vQSpBxw-gjZ--p-vo_yr2yHaXXOn_g8i=DQam_aAW3GwQ@mail.gmail.com>
 <8052505e-2dd2-e8cb-26c9-cbb5dff7d750@enterprisedb.com>
 <20211212213210.GP17618@telsasoft.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <20211212213210.GP17618@telsasoft.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/d11ae731-4366-ee5f-9ffd-0bd61f5e94a6%40enterprisedb.com>
Precedence: bulk


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