Re: The plan changes when the limit gets above ~850,000

Tom,

Thanks for the reply. You are correct, I don't really care about
the behavior at less than the maximum number of rows. I guess I was
just a bit mystified by the difference in behavior. As of now, I stop
worrying about it.

-=beeky

Tom Lane wrote:
> "Wm.A.Stafford" <stafford(at)marine(dot)rutgers(dot)edu> writes:
>
>> I looked at the plan for both versions of the query and, as Albe
>> suggested, the plans were different. I don't know how to analyze the
>> plans but I'm guessing that when the number of records returned gets
>> larger setup time is an increasingly smaller part of the entire process
>> so more time can be spent on setup. This would result in the apparent
>> inactivity of the application running the query when limit went from
>> 850,000 to 1,000,000
>>
>
> Yeah, it's supposed to do that. The question you really need to be
> asking is what's the total elapsed time to do the whole query. If
> there's a serious slowdown then the planner is choosing to cut over
> too soon, which could probably be improved by fooling with the cost
> parameter.
>
> But, do you really care about the behavior with intermediate LIMIT
> values? I would think that you might have a small limit on this query
> while doing testing, but when you go to do the real data processing
> you won't have a LIMIT at all.
>
> regards, tom lane
>