Re: [SQL] "SELECT IN" Still Broken in 7.4b

On 22 Aug 2003, Greg Stark wrote:

>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > Finally, I suspect that once we get rid of the O(N^2) behavior in the
> > executor, we will find that the next biggest bottleneck is in the
> > planner; adding more work for it to do per OR-clause item will make
> > things worse not better.
>
> But time spent in the planner doesn't matter if you're using prepared queries
> which is true for OLTP applications where there are very few queries being
> executed many many times. I hear web sites are quite popular these days.

True, but not every application is such. You still need to balance
planning time with other concerns.

> I missed the beginning of this thread so I'm not sure if it's relevant. But
> wouldn't it be possible to just check if all the clauses and see if they're
> using precisely the same index with an equality type operator? That won't
> catch things like "a BETWEEN 1 AND 2 OR a BETWEEN 5 AND 6" but it will catch
> things like "a IN (1,2,3,4,5,6)". And I don't see how it wouldn't be linear
> in the number of clauses.

But that wouldn't help unless you made sure that what was being compared
was not the same AFAICT (since the point would be to not need to check if
the rows were already returned) since a=1 or a=1 is legal if meaningless.
And that's not necessarily immediately obvious depending on the behavior
of the = operator without trying it, for example does where a='c ' or
a='c' have a redundancy or not?