Re: slow IN() clause for many cases

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > On Sun, Oct 16, 2005 at 12:03:33PM -0400, Greg Stark wrote:
> >> That's true. That's why I was wondering more about cases where the client end
> >> was going to read all the records until it found the record it's looking for
> >> or found enough records for its purposes.
>
> > I would argue that the client should simply ask for what it wants
> > rather than filtering on the client end. Then PostgreSQL has the info
> > to optimise appropriately.
>
> Certainly, if you do not supply a LIMIT, there is no justification
> at all for expecting the planner to prefer fast-start over
> minimum-total-cost.

Well figuring out when to prefer one or the other is a hard problem.
Fundamentally the server simply does not have the information it needs to
determine that available.

(I think there really ought to be a bit in the protocol that the client sends
with the query to indicate which is needed. That would be cleaner than
Oracle's /*+ FIRST_ROW */ and /*+ ALL_ROWS */ hints.)

But having it as an option is a separate question. Even if the server needs
some cajoling to actually choose the right one it's always a good thing if
it's at least possible.

--
greg