Re: A Guide to Constraint Exclusion (Partitioning)

On Sun, 2005-07-24 at 06:44 -0700, Ron Mayer wrote:
> Simon Riggs wrote:
> > in those cases you are really just maintaining the indexes for partitioning
> > purposes. On older data it may be desirable not to have lots of indexes,
> > or at least use their resources on the indexes they really do want.
> >
> > Also, if you have a List partitioned table where all rows in that table
> > have a single value, then you maintain an index for no reason other than
> > partitioning. Thats an expensive waste.
> >
> > Simply put, adding a constraint is faster and cheaper than adding an
> > pointless index. CE gives people that option.
>
>
> It seems with a partial index that whose partial index condition
> specifies a range outside that of a partition would make the expense
> much cheaper.
>
> For example, if I created a couple partial indexes
>
> ON sales_2005(year) WHERE year<2005
> ON sales_2005(year) WHERE year>2005
>
> I would think it would be a very cheap index to maintain
> (they'd be very small because they're empty, I'd think)
> and give many of the same benefits for excluding tables
> as a non-partial index on year would have given.

The indexes would only be empty if you could guarantee that no rows were
inserted that actually did match the index criteria. How would you do
that? Check Constraints. So you'd have a more complex arrangement than
if you used CE.

Internally, CE uses very similar logic to that used by partial indexes
and they actually share sections of code.

The logic is reversed however, so you are looking for tables to provably
exclude and that gives different results from the positive-proof case.
That means that partial indexes wouldn't be able to be used in all cases
and you'd end up with sequential scans: exactly what we are trying to
avoid.

...Check Constraints are very, very cheap to maintain.

Best Regards, Simon Riggs