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 match