> Bruce Momjian <maillist@candle.pha.pa.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 tabwhere 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@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
