Re: Partitioning versus autovacuum

Hello Greg,

> At the risk of forking this thread... I think there's actually a
> planner estimation bug here too.
>
I think that is not a bug. The estimation error occurred there were no
parent's statistics. We should run analyze on *partitioned table*.

Here is your test case:
create table p (i integer, j integer) partition by list (i);
create table p0 partition of p for values in (0);
create table p1 partition of p for values in (1);
insert into p select 0,generate_series(1,1000);
insert into p select 1,generate_series(1,1000);
analyze p;

explain analyze select * from q join p using (i) where j between 1 and 500;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.02..54.77 rows=500 width=8) (actual
time=0.180..2.960 rows=500 loops=1)
Hash Cond: (p0.i = q.i)
-> Append (cost=0.00..45.00 rows=1000 width=8) (actual
time=0.033..1.887 rows=1000 loops=1)
-> Seq Scan on p0 (cost=0.00..20.00 rows=500 width=8)
(actual time=0.025..0.524 rows=500 loops=1)
Filter: ((j >= 1) AND (j <= 500))
Rows Removed by Filter: 500
-> Seq Scan on p1 (cost=0.00..20.00 rows=500 width=8)
(actual time=0.014..0.499 rows=500 loops=1)
Filter: ((j >= 1) AND (j <= 500))
Rows Removed by Filter: 500
-> Hash (cost=1.01..1.01 rows=1 width=4) (actual
time=0.103..0.104 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on q (cost=0.00..1.01 rows=1 width=4) (actual
time=0.072..0.074 rows=1 loops=1)
Planning Time: 0.835 ms
Execution Time: 3.310 ms
(14 rows)

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center