Re: [HACKERS] Idea for speeding up uncorrelated subqueries

> Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Yes, the subqueries need work. We don't even do index lookups into the
> > inner plan, only sequential. Already on TODO.
>
> Huh? I don't follow that at all...

Suppose you have a subquery that returns 1000 rows. There is no code so
lookups of the inner table are indexed:

select *
from tab
where col in (select col2 from tab2)

In this case, a sequential scan of the subquery results are required. I
didn't think the subquery was executed every time it needed to see if
col1 was in the subquery.

>
> > The multiple query executions are not on the TODO list. Not sure why
> > this is happening here.
>
> After looking at subselect.c I think I understand why --- InitPlans are
> only for subqueries that are known to return a *single* reslt. When you
> have a subquery that might potentially return many, many tuples, you
> need to scan through those tuples, so we use SubPlan tactics even if
> there's not a query correlation.
>
> But this neglects the cost of re-executing the subplan over and over.
> Materializing the result should help, no? (Of course there are cases
> where it won't, such as when the subplan is just an unqualified select,
> but most of the time it should be a win, I think...)

No what Vadim is done MVCC, I would like to bug him to improve subquery
performance. We are tweeking the optimizer, but we have this huge
subquery performance problem here.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026