Re: misbehaving planer?

Darcy Buskermolen <darcyb@commandprompt.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:
    Avoid cross-datatype comparisons in the CHECK constraints, as the    planner will currently fail to prove such
conditionsfalse. 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
constantdoes not match    the data type of the column to which it is being    compared. Cross-datatype comparisons in
thesupplied 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.
        regards, tom lane