[Bizgres-general] A Guide to Constraint Exclusion (Partitioning)

[Hannu Krosing]

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 ?

-- 
Hannu Krosing <hannu at skype.net>