pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: extended stats on partitioned tables
Date: Sat, 25 Sep 2021 17:31:52 -0500
Message-ID: <20210925223152.GA7877@telsasoft.com> (raw)
In-Reply-To: <e2196aa4-4a52-6168-6f13-f50b4d0edf48@enterprisedb.com>
References: <20210923212624.GI831@telsasoft.com>
	<e2196aa4-4a52-6168-6f13-f50b4d0edf48@enterprisedb.com>

(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 (37+ messages)  latest in thread

Message-ID: <20210925223152.GA7877@telsasoft.com>
Permalink:  ../20210925223152.GA7877@telsasoft.com/
Also on:    postgresql.org/message-id/20210925223152.GA7877@telsasoft.com

 · 

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: pgsql-hackers@postgresql.org
  Cc: pryzby@telsasoft.com, tomas.vondra@enterprisedb.com
  Subject: Re: extended stats on partitioned tables
  In-Reply-To: <20210925223152.GA7877@telsasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox