Re: [HACKERS] Idea for speeding up uncorrelated subqueries

> 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