Josh Berkus <josh@agliodbs.com> writes:
> On 5/11/11 3:04 PM, Shaun Thomas wrote:
>> The original query, with our very large tables, ran for over *two hours*
>> thanks to a nested loop iterating over the subquery. My replacement ran
>> in roughly 30 seconds. If we were using a newer version of PG, we could
>> have used a CTE. But do you get what I mean? Temp tables are a fairly
>> common technique, but how would a coder know about CTEs? They're pretty
>> new, even to *us*.
> For that matter, it would be even better if PostgreSQL realized that a
> materialize of the subquery was a better execution plan, and just did it
> for you.
It does. I was a bit surprised that Shaun apparently got a plan that
didn't include a materialize step, because when I test a similar query
here, I get:
1. a hash join, until I turn off enable_hashjoin; then
2. a merge join, until I turn off enable_mergejoin; then
3. a nestloop with materialize on the subquery scan.
In 9.0 and up I can get a nestloop without materialize by also turning
off enable_material, but pre-9.0 there's no such option ...
regards, tom lane