Re: slow IN() clause for many cases

Greg Stark <gsstark@mit.edu> writes:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> It's called LIMIT and has been supported for a long time.

> And if I *don't* want to limit the number of rows I retriev?

You still need to do something proactive to inform the system that you
want a fast-start plan.  What's more, you really really do not want to
give it an unlimited license to prefer zero-start-cost plans, else you
might still be waiting for the third row when hell freezes over.

In the current system structure, the only very reasonable way to set up
incremental client processing of a query result is to use a cursor and
FETCH a few rows at a time from it.  The planner already has a hack to
give some preference to fast-start plans when planning DECLARE CURSOR.
My recollection is that it optimizes on the assumption that 10% of the
rows will be retrieved, which gives some balance between start speed and
not blowing out the total runtime unreasonably.  We've already had some
discussion about exposing that 10% figure as a GUC variable, so that you
could put a finger on the scale depending on how much of the result you
expected to fetch.  But nobody got excited enough to make it happen...
        regards, tom lane