public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: [email protected]
Subject: Re: extended stats on partitioned tables
Date: Sat, 25 Sep 2021 17:31:52 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
(Resending with -hackers)
It seems like your patch should also check "inh" in examine_variable and
statext_expressions_load.
Which leads to another issue in stable branches:
ANALYZE builds only non-inherited stats, but they're incorrectly used for
inherited queries - the rowcount estimate is worse on inheritence parents with
extended stats than without.
CREATE TABLE p(i int, j int);
CREATE TABLE p1() INHERITS(p);
INSERT INTO p SELECT a, a/10 FROM generate_series(1,9)a;
INSERT INTO p1 SELECT a, a FROM generate_series(1,999)a;
CREATE STATISTICS ps ON i,j FROM p;
VACUUM ANALYZE p,p1;
postgres=# explain analyze SELECT * FROM p GROUP BY 1,2;
HashAggregate (cost=26.16..26.25 rows=9 width=8) (actual time=2.571..3.282 rows=1008 loops=1)
postgres=# begin; DROP STATISTICS ps; explain analyze SELECT * FROM p GROUP BY 1,2; rollback;
HashAggregate (cost=26.16..36.16 rows=1000 width=8) (actual time=2.167..2.872 rows=1008 loops=1)
I guess examine_variable() should have corresponding logic to the hardcoded
!inh in analyze.c.
--
Justin
view thread (33+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: extended stats on partitioned tables
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox