Re: misbehaving planer?

On Friday 20 October 2006 08:26, Tom Lane wrote:
> Darcy Buskermolen <darcyb(at)commandprompt(dot)com> writes:
> > I have a setup in which a table has been partitioned into 30 partitions
> > on type (1 -30), however no matter what I do i can't make the planner try
> > to use constraint exclusion on it.
>
> Do you have constraint_exclusion turned on? What are the check
> constraints on the other children of tbl_ps?

Yes CE is on (you can see it in the session paste). The other child tables
have simular CHECK's of type=2, type=3, type=4 and so on.. 1 for each of the
30 tables.

> This example doesn't
> really show whether the planner is misbehaving or not.
>
> The multiple-partial-index setup on tbl_ps_type_1 looks pretty silly to
> me... it seems unlikely to buy anything except extra planning overhead.

This was a direct port from a big fat table. I agree, I'm not convinced that
the partial indexes will buy me much, but this box is so IO bound that the
planner overhead my just offset the needing to IO bigger indexes.

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster