Re: slow IN() clause for many cases

On Sun, Oct 16, 2005 at 05:09:57PM -0400, Greg Stark wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > 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.

Umm, not really. Notice how EXPLAIN has two numbers: time to first row,
time to last row. If you add limit 1 it will favour plans that return
the first row quickly. If you don't it'll favour plans that have the
lowest total execution time, even if the first tuple takes longer.

> (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.)

It's called LIMIT and has been supported for a long time.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.