extended stats on partitioned tables
Justin Pryzby <pryzby@telsasoft.com>
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, David Rowley <dgrowleyml@gmail.com>, Alvaro Herrera <alvherre@2ndquadrant.com>
Date: 2021-09-23T21:26:24Z
Lists: pgsql-hackers
extended stats objects are allowed on partitioned tables since v10. https://www.postgresql.org/message-id/flat/CAKJS1f-BmGo410bh5RSPZUvOO0LhmHL2NYmdrC_Jm8pk_FfyCA%40mail.gmail.com 8c5cdb7f4f6e1d6a6104cb58ce4f23453891651b But since 859b3003de they're not populated - pg_statistic_ext(_data) is empty. This was the consequence of a commit to avoid an error I reported with stats on inheritence parents (not partitioned tables). preceding 859b3003de, stats on the parent table *did* improve the estimate, so this part of the commit message seems to have been wrong? |commit 859b3003de87645b62ee07ef245d6c1f1cd0cedb | Don't build extended statistics on inheritance trees ... | Moreover, the current selectivity estimation code only works with individual | relations, so building statistics on inheritance trees would be pointless | anyway. |CREATE TABLE p (i int, a int, b int) PARTITION BY RANGE (i); |CREATE TABLE pd PARTITION OF p FOR VALUES FROM (1)TO(100); |TRUNCATE p; INSERT INTO p SELECT 1, a/100, a/100 FROM generate_series(1,999)a; |CREATE STATISTICS pp ON (a),(b) FROM p; |VACUUM ANALYZE p; |SELECT * FROM pg_statistic_ext WHERE stxrelid ='p'::regclass; |postgres=# begin; DROP STATISTICS pp; explain analyze SELECT a,b FROM p GROUP BY 1,2; abort; | HashAggregate (cost=20.98..21.98 rows=100 width=8) (actual time=1.088..1.093 rows=10 loops=1) |postgres=# explain analyze SELECT a,b FROM p GROUP BY 1,2; | HashAggregate (cost=20.98..21.09 rows=10 width=8) (actual time=1.082..1.086 rows=10 loops=1) So I think this is a regression, and extended stats should be populated for partitioned tables - I had actually done that for some parent tables and hadn't noticed that the stats objects no longer do anything. That begs the question if the current behavior for inheritence parents is correct.. CREATE TABLE p (i int, a int, b int); CREATE TABLE pd () INHERITS (p); INSERT INTO pd SELECT 1, a/100, a/100 FROM generate_series(1,999)a; CREATE STATISTICS pp ON (a),(b) FROM p; VACUUM ANALYZE p; explain analyze SELECT a,b FROM p GROUP BY 1,2; | HashAggregate (cost=25.99..26.99 rows=100 width=8) (actual time=3.268..3.284 rows=10 loops=1) Since child tables can be queried directly, it's a legitimate question whether we should collect stats for the table heirarchy or (since the catalog only supports one) only the table itself. I'd think that stats for the table hierarchy would be more commonly useful (but we shouldn't change the behavior in existing releases again). Anyway it seems unfortunate that statistic_ext_data still has no stxinherited. Note that for partitioned tables if I enable enable_partitionwise_aggregate, then stats objects on the child tables can be helpful (but that's also confusing to the question at hand).
Commits
-
Add stxdinherit flag to pg_statistic_ext_data
- 269b532aef55 15.0 landed
-
Build inherited extended stats on partitioned tables
- d6817032d26b 13.6 landed
- 20b9fa308ebf 15.0 landed
- ea212bd95fd2 14.2 landed
- 9d1bcf5dc11a 12.10 landed
- 491182e52909 11.15 landed
- 9211c2e38f09 10.20 landed
-
Ignore extended statistics for inheritance trees
- 76569ad6f423 12.10 landed
- ff0e7c7e8401 10.20 landed
- b3cac25f4d50 11.15 landed
- acfde7c5837d 13.6 landed
- 2cc007fd0359 14.2 landed
- 36c4bc6e725f 15.0 landed
-
Don't build extended statistics on inheritance trees
- 859b3003de87 10.10 cited
-
Tighten up relation kind checks for extended statistics
- 8c5cdb7f4f6e 10.0 cited
-
Avoid assuming that statistics for a parent relation reflect the properties of
- 427c6b5b9849 8.2.0 cited