Message-ID: <3df5c68b-13aa-53d0-c0ec-ed98e6972e2e@postgrespro.ru>
Date: Thu, 6 Oct 2022 11:02:07 +0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.3.1
Subject: Re: document the need to analyze partitioned tables
To: Laurenz Albe <laurenz.albe@cybertec.at>,
 Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Justin Pryzby <pryzby@telsasoft.com>, Robert Haas <robertmhaas@gmail.com>
Cc: =?UTF-8?Q?=c3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>,
 yuzuko <yuzukohosoya@gmail.com>, pgsql-hackers@postgresql.org
References: <20210913035409.GA10647@telsasoft.com>
 <20211008125822.GJ27491@telsasoft.com>
 <681e76ad-3a7b-ea3c-b6b0-55946239c49b@enterprisedb.com>
 <20220121180200.GM23027@telsasoft.com>
 <19c248d9-3687-9e0f-0b1a-2adad1de6fbe@enterprisedb.com>
 <CA+TgmoYNaSWQg1W3JZ62UBM9iMphBpVJr_oDxn5T1X_viOPBtw@mail.gmail.com>
 <20220315230011.GZ28503@telsasoft.com>
 <88903179-5ce2-3d4d-af43-7830372bdcb6@enterprisedb.com>
 <1fd81ddc7710a154834030133c6fea41e55c8efb.camel@cybertec.at>
Content-Language: en-US
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <1fd81ddc7710a154834030133c6fea41e55c8efb.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/3df5c68b-13aa-53d0-c0ec-ed98e6972e2e%40postgrespro.ru>
Precedence: bulk

On 10/5/22 13:37, Laurenz Albe wrote:
> On Mon, 2022-03-28 at 15:05 +0200, Tomas Vondra wrote:
>> I've pushed the last version, and backpatched it to 10 (not sure I'd
>> call it a bugfix, but I certainly agree with Justin it's worth
>> mentioning in the docs, even on older branches).
> 
> I'd like to suggest an improvement to this.  The current wording could
> be read to mean that dead tuples won't get cleaned up in partitioned tables.
> 
> 
> By the way, where are the statistics of a partitioned tables used?  The actual
> tables scanned are always the partitions, and in the execution plans that
> I have seen, the optimizer always used the statistics of the partitions.
For example, it is used to estimate selectivity of join clause:

CREATE TABLE test (id integer, val integer) PARTITION BY hash (id);
CREATE TABLE test_0 PARTITION OF test
   FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE test_1 PARTITION OF test
   FOR VALUES WITH (modulus 2, remainder 1);

INSERT INTO test (SELECT q, q FROM generate_series(1,10) AS q);
VACUUM ANALYZE test;
INSERT INTO test (SELECT q, q%2 FROM generate_series(11,200) AS q);
VACUUM ANALYZE test_0,test_1;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM test t1, test t2 WHERE t1.id = t2.val;
VACUUM ANALYZE test;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM test t1, test t2 WHERE t1.id = t2.val;

Here without actual statistics on parent table we make wrong prediction.

-- 
Regards
Andrey Lepikhov
Postgres Professional