Re: [Bizgres-general] A Guide to Constraint Exclusion

On Fri, 2005-07-15 at 01:20 +0300, Hannu Krosing wrote:
> On R, 2005-07-15 at 00:24 +0300, Hannu Krosing wrote:
> 
> > 
> > but what about _static_ exlusion based on constraints ?
> > 
> > I mean if there is a left side table with say a single partition having
> >    CHECK(id_order BETWEEN 1 AND 1000)
> > (either originally or left after eliminating other by other constraints)
> > 
> > and 3 right side partition with
> >    CHECK(key_order BETWEEN 1 AND 1000)
> >    CHECK(key_order BETWEEN 1001 AND 2000)
> >    CHECK(key_order BETWEEN 2001 AND 3000)
> > 
> > then the 3rd one could be eliminated statically from a join on
> > id_order=key_order
> 
> the simplest form of this seems to be carrying checks to both ends of
> joins before CE.
> 
> so for two partitioned tables "main" and "detail", and query
> 
> select * 
>   from main m, 
>        detail d 
>  where m.id_main = d.key_main 
>    and m.id_main in (1,7,42)
> 
> CE is done based on main.id_main in (1,7,42) and detail.key_mainin
> (1,7,42)
> 
> Or perhaps this carrying over is already done automatically by postgres
> planner before CE ?

Not sure... will check. I don't do it explicitly, but could do, given
time.

Best Regards, Simon Riggs