On Fri, Nov 19, 2010 at 1:41 PM, Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> 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). -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
