Date: Sun, 12 Dec 2021 15:32:10 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Zhihong Yu <zyu@yugabyte.com>,
	Jaime Casanova <jcasanov@systemguards.com.ec>,
	pgsql-hackers@postgresql.org
Subject: Re: extended stats on partitioned tables
Message-ID: <20211212213210.GP17618@telsasoft.com>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <8052505e-2dd2-e8cb-26c9-cbb5dff7d750@enterprisedb.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211212213210.GP17618%40telsasoft.com>
Precedence: bulk

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 ?

-- 
Justin