Re: misbehaving planer?

On Friday 20 October 2006 09:27, Tom Lane wrote:
> Darcy Buskermolen <darcyb(at)commandprompt(dot)com> writes:
> > 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.
>
> [ looks again... ] Oh, here's your problem:
>
> type | smallint |
>
> Check constraints:
> "tbl_ps_typ_1_type_check" CHECK (type = 1)
>
> That CHECK is a cross-type comparison (int2 vs int4). Per the docs:

Dohh, thanks for the sanity check. I compleatly missed that.
>
> Avoid cross-datatype comparisons in the CHECK constraints, as the
> planner will currently fail to prove such conditions false. For
> example, the following constraint will work if x is an integer
> column, but not if x is a bigint:
>
> CHECK ( x = 1 )
>
> For a bigint column we must use a constraint like:
>
> CHECK ( x = 1::bigint )
>
> The problem is not limited to the bigint data type --- it can
> occur whenever the default data type of the constant does not match
> the data type of the column to which it is being
> compared. Cross-datatype comparisons in the supplied queries are
> usually OK, just not in the CHECK conditions.
>
> So you can either cast to int2 in the CHECKs, or change the column to
> plain integer (int2 is probably not saving you anything here anyway).
>
> >> 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.
>
> Well, you should measure it, but I bet the planner wastes way more time
> considering the twenty-some indexes than is saved by avoiding one level
> of btree search, which is about the most you could hope for.

Yes mesurement will happen, step one was the partioning.

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend