Chander Ganesan <chander@otg-nc.com> 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