Thread: subquery performance and EXISTS

I am writing the subquery chapter of the book.  Tom, you once mentioned
you could get subqueries to use HASH joins, but you didn't because
someone said it may a run out of memory.

I would suggest that if it is that large a subquery, our nested loop
handling will take forever, so would never finish anyway, unless the
outer query has only a few rows.

Tom, does it still look easy to change, and if so, can you change it to
hash?  I know the real fix is for multiple target lists.

I would rather not have to go mention a serious performance workaround
in the book, because the limitation would be published perhaps for many
years.

--  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
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, does it still look easy to change, and if so, can you change it to
> hash?

We could probably do it, but there is the little problem that hash joins
only work for a very limited set of data types and operators.  This
wouldn't be a complete solution on its own.
        regards, tom lane

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom, does it still look easy to change, and if so, can you change it to
> > hash?
> 
> We could probably do it, but there is the little problem that hash joins
> only work for a very limited set of data types and operators.  This
> wouldn't be a complete solution on its own.

How hard would it be to have the EXISTS be done automatically?  The
performance problem for subqueries has bothered me for two years now.  I
don't think I know enough to fix it, though.

--  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
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> How hard would it be to have the EXISTS be done automatically?  The
> performance problem for subqueries has bothered me for two years now.

I think the ideal solution would involve rewriting the subquery into
a single-level query.  I haven't looked at how hard this would be...
        regards, tom lane