Re: [SQL] Sub-select speed. - Mailing list pgsql-sql

From Bruce Momjian
Subject Re: [SQL] Sub-select speed.
Date
Msg-id 199912180032.TAA19361@candle.pha.pa.us
Whole thread Raw
In response to Re: [SQL] Sub-select speed.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> There has been some talk of automatically rewriting queries to eliminate
> >> unnecessary sub-selects, but I don't foresee it getting done for a
> >> while yet.
> 
> > Tom, you mentioned that subselects use nested join, but they could be
> > hardcoded to use hash join.  My opinion is that this should be done if
> > it is easy.
> 
> That would probably help for uncorrelated subselects (where no variable
> from the outer query is referenced in the inner one).  For correlated
> subselects, such as this one is, hashing the subselect result isn't
> going to help much --- the real problem is that the subselect is
> repeated afresh for each outer tuple.

Oh, I was thinking in general of our EXISTS() workaround and if we could
help that by forcing hash joins.

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


pgsql-sql by date:

Previous
From: wieck@debis.com (Jan Wieck)
Date:
Subject: Re: [SQL] avg() on numeric ?
Next
From: admin
Date:
Subject: indices on join