At the risk of forking this thread... I think there's actually a
planner estimation bug here too.
Consider this test case of a simple partitioned table and a simple
join. The cardinality estimates for each partition and the Append node
are all perfectly accurate. But the estimate for the join is way off.
The corresponding test case without partitioning produces a perfect
cardinality estimate for the join.
I've never completely wrapped my head around the planner selectivity
estimations. IIRC join restrictions are treated differently from
single-relation restrictions. Perhaps what's happening here is that
the single-relation restrictions are being correctly estimated based
on the child partitions but the join restriction code hasn't been
taught the same tricks?
stark=# create table p (i integer, j integer) partition by list (i);
CREATE TABLE
stark=# create table p0 partition of p for values in (0);
CREATE TABLE
stark=# create table p1 partition of p for values in (1);
CREATE TABLE
stark=# insert into p select 0,generate_series(1,1000);
INSERT 0 1000
stark=# insert into p select 1,generate_series(1,1000);
INSERT 0 1000
stark=# analyze p0;
ANALYZE
stark=# analyze p1;
ANALYZE
stark=# create table q (i integer);
CREATE TABLE
stark=# insert into q values (0);
INSERT 0 1
stark=# analyze q;
ANALYZE
-- Query partitioned table, get wildly off row estimates for join
stark=# explain analyze select * from q join p using (i) where j
between 1 and 500;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Hash Join (cost=1.02..44.82 rows=5 width=8) (actual
time=0.060..1.614 rows=500 loops=1) │
│ Hash Cond: (p0.i = q.i)
│
│ -> Append (cost=0.00..40.00 rows=1000 width=8) (actual
time=0.030..1.127 rows=1000 loops=1) │
│ -> Seq Scan on p0 (cost=0.00..20.00 rows=500 width=8)
(actual time=0.029..0.440 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.018..0.461 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.011..0.012 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.005..0.006 rows=1 loops=1) │
│ Planning time: 0.713 ms
│
│ Execution time: 1.743 ms
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(14 rows)
-- Query non-partitioned table get accurate row estimates for join
stark=# create table pp as (Select * from p);
SELECT 2000
stark=# analyze pp;
ANALYZE
stark=# explain analyze select * from q join pp using (i) where j
between 1 and 500;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
├─────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Hash Join (cost=1.02..48.77 rows=500 width=8) (actual
time=0.027..0.412 rows=500 loops=1) │
│ Hash Cond: (pp.i = q.i)
│
│ -> Seq Scan on pp (cost=0.00..39.00 rows=1000 width=8) (actual
time=0.014..0.243 rows=1000 loops=1) │
│ Filter: ((j >= 1) AND (j <= 500))
│
│ Rows Removed by Filter: 1000
│
│ -> Hash (cost=1.01..1.01 rows=1 width=4) (actual
time=0.005..0.005 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.003..0.003 rows=1 loops=1) │
│ Planning time: 0.160 ms
│
│ Execution time: 0.456 ms
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(10 rows)
