Re: limits of constraint exclusion

On Fri, Nov 19, 2010 at 1:41 PM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com> wrote:
> And I tried to make the "it only involves a single t1 and matches a single partition" more explicit, but this didn't do it either:
>
> explain with tbl as (select id from t1 where name = 'foo')
> select * from t1, t2 where t1.id = t2.t1_id and t1.id = (select id from tbl);
>

The exclusion you have is t1_id=1 so that's what the planner can look
for. It is smart enough to deduce that t1.id = t2.t1_id and t1.id =
1 implies t1_id=1. However, it has no way to know t1.id = t2.t1_id
and t1.name = 'foo'; implies that t1.id is constant, nor what that
constant is, so cannot ever deduce that t1_id=1 is or is not going to
be true for the query.

That is, it does not evaluate your constraint expression, it proves
that the constraint is true or false based on the query, then proceeds
appropriately.

Your workaround to join with the specific table is your only real
option. Either that or add an index that lets the executor exclude
your table quickly (rather than running a full sequence scan to find
something that is not there).