Colin McGuigan <cmcguigan@earthcomber.com> writes:
> I know I can do it by adjusting cost parameters, but I was really
> curious as to why adding a "LIMIT 5000" onto a SELECT from a table with
> only 530 rows in it would affect matters at all.
The LIMIT prevents the sub-select from being flattened into the main
query. In the current code this has a side-effect of preventing any
statistical information from being used to estimate the selectivity
of the filter conditions --- so you get a default rowcount estimate
that's way too small, and that changes the shape of the join plan.
It's giving you the "right" answer for entirely the wrong reason.
regards, tom lane