Re: [HACKERS] Idea for speeding up uncorrelated subqueries

> Someone was just complaining over in the sql list about the poor
> performance of
>
> select name,description from descriptions
> where name in (select name
> from descriptions
> where description like '%Bankverbindung%');
>
> Since the inner query is uncorrelated with the outer, there's really
> no need to execute it more than once, but currently it's re-executed
> each time through the outer plan.
>
> I wonder whether it wouldn't be a good idea to force a Materialize
> node to be added to the top of an uncorrelated subplan? Then at
> least the re-executions would be pretty cheap...

Yes, the subqueries need work. We don't even do index lookups into the
inner plan, only sequential. Already on TODO. The multiple query
executions are not on the TODO list. Not sure why this is happening
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