On Friday 20 October 2006 09:27, Tom Lane wrote:
> Darcy Buskermolen <[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

Reply via email to