
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, Pennsylvania 19026

Wed, 29 Dec 1999 09:11:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
subquery performance and EXISTS
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

Wed, 29 Dec 1999 11:07:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: subquery performance and EXISTS 
> 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, Pennsylvania 19026

Wed, 29 Dec 1999 16:20:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: subquery performance and EXISTS
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

Wed, 29 Dec 1999 17:25:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: subquery performance and EXISTS 