Re: misbehaving planer?

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:

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.

regards, tom lane