Chander Ganesan <chander@xxxxxxxxxx> writes: > In theory, if the table with 6 columns was the child of the table with > 200+ columns, and a PK-FK relationship existed, then the optimizer > wouldn't need to check for the existence of the rows, since the PK-FK > relationship would indicate that the rows did/do exist. No, that's still not right. With a LEFT JOIN you know that each row of the narrow table will produce at least one row in the join view. What you don't know is whether the row could produce more than one join row --- ie, is there more than one wide-table row that joins to it? To optimize away the join, the planner would have to find a unique constraint on the wide table's join column(s). This is certainly doable in principle, though I find it questionable whether the planner should spend cycles on every join query checking for something that won't be true in the vast majority of real-world queries. The main reason we have not considered it to date is that the correctness of the plan would then depend on a constraint that could get dropped --- but the plan would not fail when run, as indeed it wouldn't be touching that table at all. We really need some plan-invalidation infrastructure to force re-planning whenever a table's schema changes, and only then will it be safe for the planner to start depending on constraints for correctness- related decisions. (This is why constraint exclusion is off by default at the moment.) I've been wanting plan invalidation for a long time ... maybe it will finally happen in 8.3. regards, tom lane
