Hello,
Consider two tables:
contacts:
cid integer primary key,
pid integer not null,
cpid integer
...
pinfo:
pid integer,
...
pinfo is a parent table with two partitions pinfo_p00 and pinfo_p01, all three have primary keys on pid and partitions have proper constraints
that guarantee pid uniqueness across them.
Now here's the part of the query:
select *
from contacts c
left join pinfo pi on (pi.pid = c.cpid)
QUERY PLAN
-> Nested Loop Left Join (cost=0.00..444.90 rows=1515 width=408) (actual time=0.108..5.561 rows=44 loops=1)
Join Filter: (pi.pid = c.cpid)
-> Index Scan using ix_contacts_pid on contacts c (cost=0.00..14.84 rows=50 width=26) (actual time=0.038..0.425 rows=44 loops=1)
Index Cond: (pid = 167)
-> Append (cost=0.00..8.56 rows=3 width=386) (actual time=0.067..0.090 rows=1 loops=44)
-> Index Scan using pk_pinfo on pinfo pi (cost=0.00..1.15 rows=1 width=386) (actual time=0.008..0.008 rows=0 loops=44)
Index Cond: (pi.pid = c.cpid)
-> Index Scan using pk_pinfo_p00 on pinfo_p00 pi (cost=0.00..3.23 rows=1 width=46) (actual time=0.011..0.014 rows=0 loops=44)
Index Cond: (pi.pid = c.cpid)
-> Index Scan using pk_pinfo_p01 on pinfo_p01 pi (cost=0.00..4.19 rows=1 width=46) (actual time=0.011..0.014 rows=0 loops=44)
Index Cond: (pi.pid = c.cpid)
How come that outermost join expects 1515 rows given the row estimations of the inner and outer nested loop's parts?
